package com.shuhe.handler;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.shuhe.JettyServer;
import com.shuhe.common.*;
import com.shuhe.domain.api.ApiAlgorithmRequest;
import com.shuhe.domain.fullText.ChatQuestion;
import com.shuhe.domain.mxgc.*;
import com.shuhe.domain.token.Token;
import com.shuhe.metastore.AISql;
import com.shuhe.metastore.DBSql;
import com.shuhe.metastore.MetaMysql;
import com.shuhe.metastore.Neo4jDB;
import org.apache.log4j.Logger;

import javax.servlet.http.HttpServlet;
import java.sql.*;
import java.util.*;

import static com.shuhe.JettyServer.algorithmUrl;
import static com.shuhe.JettyServer.globalProperties;
import static com.shuhe.common.CommonFunction.*;
import static com.shuhe.common.MyDateUtils.getNowDate;
import static com.shuhe.handler.CommonHandler.*;
import static com.shuhe.handler.MetaHandler.standard_create;


public class ServiceDetail extends HttpServlet {

    private static Logger logger = Logger.getLogger(ServiceDetail.class);


    public static String reqUpdateColumnConfig(JSONObject bodyJson) throws Exception {
        Connection dbConn = null;
        Connection metaConn = null;
        try {
            String table_catalog = bodyJson.getString("table_catalog");
            String table_schema = bodyJson.getString("table_schema");
            String ori_table_schema = bodyJson.getString("table_schema");
            String table_name = bodyJson.getString("table_name");
            String table_comment = bodyJson.getString("table_comment");
            if (DBSql.dbType.equals("sqlServer")) {
                table_schema = table_catalog + "." + table_schema;
            }
            String ly = bodyJson.getString("ly");
            metaConn = MetaMysql.connect();
            ArrayList<String> sqls = new ArrayList<>();
            String sql = "select db_name from mxgc_user_dbs where db_name = '" + table_schema + "'";
            ResultSet rs = MetaMysql.querySql(metaConn, sql);
            if (!rs.next()) {
                //不存在数据库配置
                sql =
                        "insert into mxgc_user_dbs(id, db_name, db_comment, user_name, is_write, show_order, db_type)"
                                + " " + "values ('" + CommonFunction.getUUID() + "', '" + table_schema + "', '" + table_schema + "', " + "'mxgc_common', '', '10','db')";
                sqls.add(sql);
            }

            sql =
                    "select table_name from mxgc_user_table where db_name = '" + table_schema + "' and table_name='" + table_name + "'";
            rs = MetaMysql.querySql(metaConn, sql);
            if (!rs.next()) {
                //不存在数据表配置
                sql = "insert into mxgc_user_table(id, user_name, db_name, table_name, table_comment, " +
                        "table_type, show_order) values ('" + CommonFunction.getUUID() + "', 'mxgc_common', '" + table_schema + "', '" + table_name + "', '" + table_comment + "', '0', '30')";
                sqls.add(sql);
            }

            if (ly.equals("db")) {
                dbConn = DBSql.connect();
                //根据数据库更新
                if (DBSql.dbType.equals("sqlServer")) {
                    sql =
                            "SELECT\n" + "\ttable_name = CONVERT ( VARCHAR ( 100 ), d.name ),\n" + "\ttable_comment " + "=" + " " + "CONVERT ( VARCHAR ( 100 ), f.value ),\n" + "\tordinal_position = " + "CONVERT ( " + "VARCHAR ( 100 ), a" + ".colorder ),\n" + "\tcolumn_name = CONVERT ( VARCHAR ( " + "100 ), a.name ),\n" + "\tdata_type = " + "CONVERT ( VARCHAR ( 100 ), b.name )," + "\n" + "\tcharacter_maximum_length = CONVERT ( VARCHAR ( " + "100 ), " + "COLUMNPROPERTY( a.id, a.name, " + "'PRECISION' ) ),\n" + "\tcolumn_comment =" + " CONVERT " + "( VARCHAR ( 100 ), ISNULL( g.[value], '' ) ),\n" + "\tnumeric_precision = CONVERT ( VARCHAR" + " ( 100 ), ISNULL( " + "COLUMNPROPERTY( a.id, a.name, " + "'Scale' ), 0 ) ) \n" + "FROM\n" + "\tsyscolumns a\n" + "\tLEFT JOIN systypes b ON a.xusertype = b" + ".xusertype\n" + "\tINNER " + "JOIN sysobjects d ON a.id = d.id \n" + "\tAND d.xtype = 'U' \n" + "\tAND d.name <> " + "'dtproperties' \n" + "\tAND d.name NOT LIKE 'sys%'\n" + "\tLEFT JOIN syscomments e ON a" + ".cdefault = e.id\n" + "\tLEFT JOIN sys.extended_properties g ON a.id = g" + ".major_id \n" + "\tAND a.colid = g.minor_id\n" + "\tLEFT JOIN sys" + ".extended_properties f ON d.id = f.major_id \n" + "\tAND f.minor_id = " + "0\n" + "\tJOIN (\n" + "\tSELECT\n" + "\t\tsys.objects.name AS " + "table_name,\n" + "\t\tsys.schemas.name AS schema_name \n" + "\tFROM\n" + "\t\tsys.objects,\n" + "\t\tsys.schemas \n" + "\tWHERE\n" + "\t" + "\tsys.objects.type= 'U' \n" + "\t\tAND sys.objects.schema_id = sys" + ".schemas.schema_id \n" + "\t\tAND sys.schemas.name= '" + ori_table_schema + "' " + "\n" + "\t\tAND sys.objects.name= '" + table_name + "' \n" + "\t) h ON d.name= h" + ".table_name \n" + "ORDER BY\n" + "\ta.id,\n" + "\ta.colorder\t";
                    String url_db = ";databaseName=" + table_catalog;
                    dbConn = DBSql.connectByUrl(DBSql.url + url_db);
                }
                if (DBSql.dbType.equals("postgresql")) {
                    sql = "SELECT distinct\n" + "\tcol.table_catalog,\n" + "\tcol.table_schema,\n" + "\tcol" +
                            ".TABLE_NAME,\n" + "\tcol.COLUMN_NAME,\n" + "\tcol.ordinal_position,\n" + "CASE\n" + "\t" + "\t\n"
                            + "\t\tWHEN d.description IS NULL THEN\n" + "\t\tCOLUMN_NAME ELSE d.description \n" +
                            "\tEND " + "column_comment,\n" + "\tdata_type,\n" + "\tcharacter_maximum_length \n" +
                            "FROM\n" + "\tinformation_schema.COLUMNS col\n" + "\tJOIN pg_class C ON C.relname = col" + ".\n" + "\tTABLE_NAME " + "LEFT JOIN pg_description d ON d.objoid = C.OID \n" + "\tAND d" + ".objsubid = col" + ".ordinal_position\n" + "where table_schema = '" + ori_table_schema + "' and table_name =" + " '" + table_name + "'\n" + "ORDER BY\n" + "\tcol.TABLE_NAME,\n" + "\tcol" + ".ordinal_position";
                }
                if (DBSql.dbType.equals("mysql")) {
                    sql = "select table_catalog, table_schema, TABLE_NAME, COLUMN_NAME, ordinal_position, " +
                            "column_comment, " + "data_type, character_maximum_length\n" + "  from INFORMATION_SCHEMA" +
                            ".columns" + " where table_schema = '" + table_schema + "' and " + "table_name " + "= '" + table_name + "' " + "order by ordinal_position";

                }
                rs = DBSql.querySql(dbConn, sql);
            }
            sql =
                    "delete from mxgc_user_column where db_name = '" + table_schema + "' and table_name = '" + table_name +
                            "'";
            sqls.add(sql);

            while (rs.next()) {
                String column_name = rs.getString("column_name").toLowerCase();
                String column_comment = rs.getString("column_comment");
                String ordinal_position = rs.getString("ordinal_position");
                String data_type = rs.getString("data_type");
                data_type = convertFieldType(data_type);
                sql = "insert into mxgc_user_column(db_name, table_name, column_name, column_comment, " +
                        "ordinal_position, data_type) values ('" + table_schema + "', '" + table_name + "', '" + column_name + "', '" + column_comment + "', '" + ordinal_position + "', '" + data_type + "')";
                sqls.add(sql);
            }
            if (sqls.size() > 3) {
                MetaMysql.batchExecSql(metaConn, sqls);
            }

            CommonResponseBody commonResp = new CommonResponseBody();
            commonResp.setSuccess();
            String respJson = resultSuccess(commonResp);
            return respJson;
        } finally {
            closeDb(dbConn);
            closeDb(metaConn);
        }
    }


    public static String reqMakeCreateSql(JSONObject bodyJson) throws Exception {
        Connection dbConn = null;
        Connection metaConn = null;
        try {
            String table_catalog = bodyJson.getString("table_catalog");
            String table_schema = bodyJson.getString("table_schema");
            String table_name = bodyJson.getString("table_name");
            String sql = "";
            dbConn = DBSql.connect();
            if (DBSql.dbType.equals("postgresql")) {
                sql = "SELECT\n" + "\tcol.table_catalog,\n" + "\tcol.table_schema,\n" + "\tcol.TABLE_NAME,\n" +
                        "\tcol.COLUMN_NAME,\n" + "\tcol.ordinal_position,\n" + "CASE\n" + "\t\t\n" + "\t\tWHEN d" +
                        ".description " + "IS NULL THEN\n" + "\t\tCOLUMN_NAME ELSE d.description \n" + "\tEND " +
                        "column_comment," + "\n" + "\tdata_type,\n" + "\tcharacter_maximum_length \n" + "FROM\n" +
                        "\tinformation_schema" + ".COLUMNS" + " col\n" + "\tJOIN pg_class C ON C.relname = col.\n" +
                        "\tTABLE_NAME LEFT JOIN " + "pg_description d ON d.objoid = C.OID \n" + "\tAND d.objsubid = " + "col" + ".ordinal_position\n" + "where " + "table_schema = '" + table_schema + "' and table_name = '" + table_name + "'\n" + "ORDER BY\n" + "\tcol.TABLE_NAME,\n" + "\tcol.ordinal_position";
            }
            if (DBSql.dbType.equals("sqlServer")) {
                sql = "SELECT\n" + "\ttable_name = CONVERT ( VARCHAR ( 100 ), d.name ),\n" + "\ttable_comment = " +
                        "CONVERT " + "(" + " VARCHAR ( 100 ), f.value ),\n" + "\tordinal_position = CONVERT ( " +
                        "VARCHAR" + " ( " + "100 )," + " a" + ".colorder " + ")," + "\n" + "\tcolumn_name = CONVERT " +
                        "(" + " VARCHAR ( 100 ), " + "a" + ".name )," + "\n" + "\tdata_type = " + "CONVERT " + "( " +
                        "VARCHAR " + "( " + "100 ), b.name ),\n" + "\tcharacter_maximum_length = " + "CONVERT ( " +
                        "VARCHAR " + "( " + "100 ), COLUMNPROPERTY( a.id, " + "a" + ".name, " + "'PRECISION' " +
                        ") )" + ",\n" + "\tcolumn_comment = CONVERT ( VARCHAR ( 100 ), " + "ISNULL(" + " g.[value" +
                        "]," + " '' ) )" + "," + "\n" + "\tnumeric_precision = CONVERT ( VARCHAR" + " ( 100 " +
                        "), " + "ISNULL(" + " " + "COLUMNPROPERTY( a" + ".id," + " a" + ".name, 'Scale' )" + ", 0" +
                        " ) ) \n" + "FROM\n" + "\tsyscolumns a\n" + "\tLEFT " + "JOIN " + "systypes b ON a" +
                        ".xusertype = b" + ".xusertype\n" + "\tINNER JOIN " + "sysobjects d ON a.id " + "= d.id " +
                        "\n" + "\tAND d.xtype =" + " " + "'U' \n" + "\tAND " + "d" + ".name <> 'dtproperties' " +
                        "\n" + "\tAND d.name NOT LIKE " + "'sys%'\n" + "\tLEFT JOIN " + "syscomments e ON a" +
                        ".cdefault = e" + ".id\n" + "\tLEFT JOIN " + "sys" + ".extended_properties g ON a.id "
                        + "= " + "g" + ".major_id \n" + "\tAND a.colid" + " = g" + ".minor_id\n" + "\tLEFT " +
                        "JOIN sys.extended_properties " + "f " + "ON d" + ".id" + " =" + " f" + ".major_id " +
                        "\n" + "\tAND f.minor_id = 0\n" + "\tJOIN (\n" + "\tSELECT\n" + "\t" + "\tsys" +
                        ".objects.name AS table_name,\n" + "\t\tsys.schemas.name AS schema_name " + "\n" + "\tFROM" + "\n" + "\t\tsys.objects,\n" + "\t\tsys.schemas \n" + "\tWHERE\n" + "\t\tsys" + ".objects.type= " + "'U' \n" + "\t\tAND sys.objects.schema_id = sys.schemas" + ".schema_id " + "\n" + "\t\tAND sys" + ".schemas.name= '" + table_schema + "' \n" + "\t" + "\tAND sys" + ".objects.name= '" + table_name + "' \n" + "\t) h ON d.name= h" + ".table_name \n" + "ORDER BY\n" + "\ta.id,\n" + "\ta.colorder\t";
                String url_db = ";databaseName=" + table_catalog;
                dbConn = DBSql.connectByUrl(DBSql.url + url_db);
            }
            if (DBSql.dbType.equals("mysql")) {
                sql = "select table_catalog, table_schema, TABLE_NAME, COLUMN_NAME, ordinal_position, column_comment,"
                        + " " + "data_type, character_maximum_length\n" + "  from INFORMATION_SCHEMA.columns where " +
                        "table_schema = '" + table_schema + "' and table_name" + " " + "= '" + table_name + "' order "
                        + "by"
                        + " " + "ordinal_position";
            }
            ResultSet rs = DBSql.querySql(dbConn, sql);
            sql = "create table " + CommonFunction.getFirstPingYin(table_name) + "(\n";
            String sqlComm = "";
            ArrayList<String> columns = new ArrayList<>();
            while (rs.next()) {
                String column_name = rs.getString("column_name");
                String column_comment = rs.getString("column_comment");
                String character_maximum_length = rs.getString("character_maximum_length");
                String data_type = rs.getString("data_type");
                column_name = CommonFunction.getFirstPingYin(column_name);
                int i = 0;
                while (columns.indexOf(column_name) >= 0) {
                    column_name = column_name + i;
                    i++;
                }
                columns.add(column_name);
                sql = sql + column_name + " " + data_type + " (" + character_maximum_length + "),\n";
                sqlComm =
                        sqlComm + "COMMENT ON COLUMN " + table_name + "." + column_name + " is '" + column_comment +
                                "';\n";
            }
            sql = sql.substring(0, sql.length() - 2) + ");\n" + sqlComm;
            CommonResponseBody commonResp = new CommonResponseBody();
            commonResp.setSuccess();
            commonResp.setExtraStr(sql);
            String respJson = resultSuccess(commonResp);
            logger.info(respJson);
            return respJson;
        } finally {
            closeDb(dbConn);
            closeDb(metaConn);
        }
    }

    public static String reqEditLimitConfig(JSONObject bodyJson) throws Exception {
        Connection metaConn = null;
        try {
            metaConn = MetaMysql.connect();
            ArrayList<String> sqls = new ArrayList<>();

            String sql = "delete from mxgc_user_config";
            sqls.add(sql);

            JSONArray ja = bodyJson.getJSONArray("data");

            for (int i = 0; i < ja.size(); i++) {
                JSONObject jo = ja.getJSONObject(i);
                String id = jo.getString("id");
                String save_row = jo.getString("save_row");
                String create_table = jo.getString("create_table");
                String user_name = jo.getString("user_name");
                String show_row = jo.getString("show_row");
                sql =
                        "insert into mxgc_user_config(id, save_row, create_table, user_name, show_row) values(" + "'" + id + "', '" + save_row + "', '" + create_table + "', '" + user_name + "', '" + show_row + "')";
                sqls.add(sql);
            }
            MetaMysql.batchExecSql(metaConn, sqls);

            CommonResponseBody commonResp = new CommonResponseBody();
            commonResp.setSuccess();
            String respJson = resultSuccess(commonResp);
            return respJson;
        } finally {
            closeDb(metaConn);
        }
    }

    public static String reqEditDbConfig(JSONObject bodyJson) throws Exception {
        Connection metaConn = null;
        try {
            metaConn = MetaMysql.connect();
            ArrayList<String> sqls = new ArrayList<>();

            String sql = "delete from mxgc_user_dbs";
            sqls.add(sql);

            JSONArray ja = bodyJson.getJSONArray("data");

            for (int i = 0; i < ja.size(); i++) {
                JSONObject jo = ja.getJSONObject(i);
                String id = jo.getString("id");
                String db_name = jo.getString("db_name");
                String db_comment = jo.getString("db_comment");
                String user_name = jo.getString("user_name");
                String is_write = jo.getString("is_write");
                String show_order = jo.getString("show_order");
                String db_type = jo.getString("db_type");
                sql = "insert into mxgc_user_dbs(id, db_name, db_comment, user_name, is_write, show_order, " +
                        "db_type) values('" + id + "','" + db_name + "','" + db_comment + "','" + user_name + "','" + is_write + "','" + show_order + "','" + db_type + "')";
                sqls.add(sql);
            }
            MetaMysql.batchExecSql(metaConn, sqls);

            CommonResponseBody commonResp = new CommonResponseBody();
            commonResp.setSuccess();
            String respJson = resultSuccess(commonResp);
            return respJson;
        } finally {
            closeDb(metaConn);
        }
    }


    public static String reqEditColumnConfig(JSONObject bodyJson) throws Exception {
        Connection metaConn = null;
        try {
            String table_schema = bodyJson.getString("table_schema");
            String table_name = bodyJson.getString("table_name");
            metaConn = MetaMysql.connect();
            ArrayList<String> sqls = new ArrayList<>();

            String sql =
                    "delete from mxgc_user_column where db_name = '" + table_schema + "' and table_name = '" + table_name +
                            "'";
            sqls.add(sql);

            JSONArray ja = bodyJson.getJSONArray("data");

            for (int i = 0; i < ja.size(); i++) {
                JSONObject jo = ja.getJSONObject(i);
                String column_name = jo.getString("column_name");
                String column_comment = jo.getString("column_comment");
                String ordinal_position = jo.getString("ordinal_position");
                String data_type = jo.getString("data_type");
                sql = "insert into mxgc_user_column(db_name, table_name, column_name, column_comment, " +
                        "ordinal_position, data_type) values ('" + table_schema + "', '" + table_name + "', '" + column_name + "', '" + column_comment + "', '" + ordinal_position + "', '" + data_type + "')";
                sqls.add(sql);
            }
            MetaMysql.batchExecSql(metaConn, sqls);

            CommonResponseBody commonResp = new CommonResponseBody();
            commonResp.setSuccess();
            String respJson = resultSuccess(commonResp);
            return respJson;
        } finally {
            closeDb(metaConn);
        }
    }


    public static String convertFieldType(String data_type) {
        if (data_type.indexOf("char") >= 0 || data_type.indexOf("text") >= 0) {
            data_type = "varchar";
        }
        if (data_type.startsWith("timestamp") && !DBSql.dbType.equals("sqlServer")) {
            data_type = "timestamp";
        }
        if (data_type.indexOf("datetime") >= 0) {
            data_type = "timestamp";
        }
        if (data_type.indexOf("int") >= 0 || data_type.indexOf("int4") >= 0 || data_type.indexOf("decimal") >= 0) {
            data_type = "numeric";
        }
        return data_type;
    }


    public static String reqDbConfig_getDb(JSONObject bodyJson) throws Exception {
        Connection dbConn = null;
        try {
            dbConn = DBSql.connect();
            DatabaseMetaData metaData = dbConn.getMetaData();

            ResultSet resultSet = metaData.getCatalogs();
            ArrayList<Object> datas = new ArrayList();
            DynamicBean dynamicBean = new DynamicBean();
            while (resultSet.next()) {
                String cat = resultSet.getString("TABLE_CAT");
                Map<String, Object> addProperties = new HashMap();
                addProperties.put("label", cat);
                addProperties.put("value", cat);
                Object obj = ReflectUtil.getTarget(dynamicBean, addProperties);
                datas.add(obj);
            }
            SqlResponseResult result = new SqlResponseResult();
            result.setDatas(datas);
            return setSuccessBodyByResult(result);
        } finally {
            closeDb(dbConn);
        }
    }

    public static String reqDbConfig_getSchema(JSONObject bodyJson) throws Exception {
        Connection dbConn = null;
        try {
            String table_catalog = bodyJson.getString("table_catalog");
            if (DBSql.dbType.equals("sqlServer")) {
                String url_db = ";databaseName=" + table_catalog;
                dbConn = DBSql.connectByUrl(DBSql.url + url_db);
            } else {
                dbConn = DBSql.connect();
            }
            ArrayList<Object> datas = new ArrayList();
            DatabaseMetaData metaData = dbConn.getMetaData();
            if (metaData.getDatabaseProductName().equals("MySQL")) {
                DynamicBean dynamicBean = new DynamicBean();
                Map<String, Object> addProperties = new HashMap();
                addProperties.put("label", table_catalog);
                addProperties.put("value", table_catalog);
                Object obj = ReflectUtil.getTarget(dynamicBean, addProperties);
                datas.add(obj);
            }
            if (metaData.getDatabaseProductName().equals("PostgreSQL")) {
                String sql =
                        "select table_schema as label, table_schema as value from information_schema.COLUMNS " +
                                "where " +
                                "table_catalog = '" + table_catalog + "' group by table_schema";
                ResultSet rs = DBSql.querySql(dbConn, sql);
                datas = getResultSet(rs);
            }
            SqlResponseResult result = new SqlResponseResult();
            result.setDatas(datas);
            return setSuccessBodyByResult(result);
        } finally {
            closeDb(dbConn);
        }
    }

    public static String reqDbConfig_getTable(JSONObject bodyJson) throws Exception {
        Connection dbConn = null;
        try {
            String table_catalog = bodyJson.getString("table_catalog");
            String table_schema = bodyJson.getString("table_schema");
            String sql = "";
            ArrayList<Object> datas = new ArrayList();
            dbConn = DBSql.connect();
            if (DBSql.dbType.equals("postgresql")) {
                sql = "select value, label from\n" + "(SELECT tb.table_name as value, " + "case when d" +
                        ".description" + " is null then tb.table_name else d.description end as label\n" + "FROM " +
                        "information_schema.tables tb\n" + "         JOIN pg_class c ON c.relname = tb.table_name\n" + " " +
                        "   " + "   " + "  JOIN pg_namespace n ON c.relnamespace = n.oid and n.nspname='" + table_schema +
                        "'\n" + "    " + " " + "    " + "LEFT JOIN pg_description d ON d.objoid = c.oid AND d" +
                        ".objsubid " + "=" + " " + "'0'\n" + "WHERE " + "tb" + ".table_schema " + "= '" + table_schema + "' and tb" + ".table_catalog" + " = '" + table_catalog + "' )" + " a\n" + "group by " + "value, label";
                ResultSet rs = DBSql.querySql(dbConn, sql);
                datas = getResultSet(rs);
            }
            if (DBSql.dbType.equals("sqlServer")) {
                String url_db = ";databaseName=" + table_catalog;
                dbConn = DBSql.connectByUrl(DBSql.url + url_db);
                sql = "select table_name as value, " + "case when table_comment is null then table_name else " +
                        "table_comment end as label from\n" + "(\n" + "SELECT\n" + "\ttable_name = CONVERT ( VARCHAR "
                        + "( " + "100 )" + ", d.name ),\n" + "\ttable_comment = CONVERT ( VARCHAR ( 100 ), f.value )"
                        + ",\n" + "\tordinal_position" + " = CONVERT ( VARCHAR ( 100 ), a.colorder ),\n" +
                        "\tcolumn_name = " + "CONVERT ( VARCHAR ( 100 ), a" + ".name ),\n" + "\tdata_type = " +
                        "CONVERT" + " ( VARCHAR ( 100 ), b" + ".name ),\n" + "\tcharacter_maximum_length = CONVERT" + " ( VARCHAR (" + " 100 ), COLUMNPROPERTY( a" + ".id, a" + ".name, " + "'PRECISION' ) )," + "\n" + "\tcolumn_comment = CONVERT ( VARCHAR ( 100 ), " + "ISNULL" + "( g" + ".[value], " + "'' ) )," + "\n" + "\tnumeric_precision = CONVERT ( VARCHAR ( 100 ), " + "ISNULL( " + "COLUMNPROPERTY( a" + ".id, a.name, " + "'Scale' ), 0 ) ) \n" + "FROM\n" + "\tsyscolumns a\n" + "\tLEFT JOIN " + "systypes b ON a.xusertype = b.xusertype\n" + "\tINNER" + " JOIN sysobjects d ON " + "a.id" + " = d.id \n" + "\tAND d.xtype = 'U' \n" + "\tAND d.name <> " + "'dtproperties' \n" + "\tAND d" + ".name NOT LIKE 'sys%'\n" + "\tLEFT " + "JOIN syscomments e " + "ON a.cdefault = e.id\n" + "\tLEFT" + " " + "JOIN sys" + ".extended_properties g ON a.id = g" + ".major_id \n" + "\tAND a.colid = g" + ".minor_id\n" + "\tLEFT JOIN sys" + ".extended_properties f ON d.id = f.major_id \n" + "\tAND f.minor_id = 0\n" + "\tJOIN (\n" + "\tSELECT\n" + "\t\tsys.objects.name AS " + "table_name,\n" + "\t\tsys" + ".schemas.name AS schema_name \n" + "\tFROM\n" + "\t\tsys" + ".objects,\n" + "\t\tsys" + ".schemas \n" + "\tWHERE\n" + "\t\tsys.objects.type= 'U' \n" + "\t\tAND sys" + ".objects.schema_id = sys.schemas.schema_id \n" + "\t\tAND sys.schemas.name= " + "'" + table_schema + "' \n" + "\t) h ON d.name= h.table_name \n" + "\t) tt\n" + "\tgroup by " + "table_name, table_comment";
                ResultSet rs = DBSql.querySql(dbConn, sql);
                datas = getResultSet(rs);
            }
            if (DBSql.dbType.equals("mysql")) {
                // 获取所有表的信息
                sql = "SELECT table_name, table_comment FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ?";
                PreparedStatement pstmt = dbConn.prepareStatement(sql);
                pstmt.setString(1, table_catalog);
                ResultSet rs = pstmt.executeQuery();
                DynamicBean dynamicBean = new DynamicBean();
                while (rs.next()) {
                    Map<String, Object> addProperties = new HashMap();
                    String table_comment = rs.getString("table_comment");
                    String table_name = rs.getString("table_name");
                    if (table_comment.length() <= 0) {
                        table_comment = table_name;
                    }
                    addProperties.put("label", table_comment);
                    addProperties.put("value", table_name);
                    Object obj = ReflectUtil.getTarget(dynamicBean, addProperties);
                    datas.add(obj);
                }
            }
            SqlResponseResult result = new SqlResponseResult();
            result.setDatas(datas);
            return setSuccessBodyByResult(result);
        } finally {
            closeDb(dbConn);
        }
    }

    public static String reqDbConfig_getColumn(JSONObject bodyJson) throws Exception {
        Connection dbConn = null;
        try {
            String table_catalog = bodyJson.getString("table_catalog");
            String table_schema = bodyJson.getString("table_schema");
            String table_name = bodyJson.getString("table_name");
            dbConn = DBSql.connect();
            ArrayList<Object> datas = new ArrayList();
            String sql = "";
            if (DBSql.dbType.equals("postgresql")) {
                sql = "SELECT distinct\n" + "\tcol.table_catalog,\n" + "\tcol.table_schema,\n" + "\tcol" +
                        ".TABLE_NAME,\n" + "\tcol.COLUMN_NAME,\n" + "\tcol.ordinal_position,\n" + "CASE\n" + "\t\t\n" + "\t" + "\tWHEN d.description IS NULL THEN\n" + "\t\tCOLUMN_NAME ELSE d.description \n" + "\tEND " + "column_comment,\n" + "\tdata_type,\n" + "\tcharacter_maximum_length \n" + "FROM\n" + "\tinformation_schema.COLUMNS col\n" + "\tJOIN pg_class C ON C.relname = col.\n" + "\tTABLE_NAME " + "LEFT " + "JOIN pg_description d ON d.objoid = C.OID \n" + "\tAND d.objsubid = col" + ".ordinal_position" + "\n" + "where" + " table_schema = '" + table_schema + "' and table_name = '" + table_name + "'\n" + "ORDER BY\n" + "\tcol.TABLE_NAME,\n" + "\tcol.ordinal_position";
                ResultSet rs = DBSql.querySql(dbConn, sql);
                datas = getResultSet(rs);
            }
            if (DBSql.dbType.equals("sqlServer")) {
                String url_db = ";databaseName=" + table_catalog;
                dbConn = DBSql.connectByUrl(DBSql.url + url_db);
                sql = "SELECT\n" + "\ttable_name = CONVERT ( VARCHAR ( 100 ), d.name ),\n" + "\ttable_comment = " +
                        "CONVERT ( VARCHAR ( 100 ), f.value ),\n" + "\tordinal_position = CONVERT ( VARCHAR ( 100 ), "
                        + "a" + ".colorder ),\n" + "\tcolumn_name = CONVERT ( VARCHAR ( 100 ), a.name ),\n" +
                        "\tdata_type = " + "CONVERT ( VARCHAR ( 100 ), b.name ),\n" + "\tcharacter_maximum_length = "
                        + "CONVERT ( VARCHAR ( " + "100 )" + ", COLUMNPROPERTY( a.id, a.name, " + "'PRECISION' ) ),"
                        + "\n" + "\tcolumn_comment = " + "CONVERT ( VARCHAR" + " ( 100 ), ISNULL( g.[value], '' ) "
                        + ")," + "\n" + "\tnumeric_precision = " + "CONVERT ( VARCHAR ( 100 ), " + "ISNULL( " +
                        "COLUMNPROPERTY" + "( a.id, a.name, " + "'Scale' ), 0 )" + " ) \n" + "FROM\n" +
                        "\tsyscolumns " + "a\n" + "\tLEFT JOIN systypes b ON a.xusertype = b" + ".xusertype\n" +
                        "\tINNER JOIN sysobjects " + "d " + "ON a.id = d.id \n" + "\tAND d.xtype = " + "'U' \n" +
                        "\tAND d.name <> " + "'dtproperties' \n" + "\tAND" + " d.name NOT LIKE 'sys%'\n" + "\tLEFT " + "JOIN syscomments" + " e ON a.cdefault = e.id\n" + "\tLEFT " + "JOIN sys" + ".extended_properties g ON a" + ".id = g.major_id \n" + "\tAND a.colid = g.minor_id\n" + "\tLEFT " + "JOIN sys" + ".extended_properties f ON d.id = f.major_id \n" + "\tAND f" + ".minor_id = 0\n" + "\tJOIN (\n" + "\tSELECT\n" + "\t\tsys.objects.name AS table_name,\n" + "\t\tsys" + ".schemas" + ".name AS schema_name \n" + "\tFROM\n" + "\t\tsys.objects,\n" + "\t" + "\tsys.schemas \n" + "\tWHERE\n" + "\t\tsys.objects.type= 'U' \n" + "\t\tAND sys" + ".objects.schema_id = sys" + ".schemas.schema_id \n" + "\t\tAND sys.schemas" + ".name= '" + table_schema + "' \n" + "\t" + "\tAND sys.objects.name= '" + table_name + "' \n" + "\t) h ON d.name= h.table_name \n" + "ORDER BY\n" + "\ta.id,\n" + "\ta.colorder\t";
                ResultSet rs = DBSql.querySql(dbConn, sql);
                datas = getResultSet(rs);
            }
            if (DBSql.dbType.equals("mysql")) {
                sql = "select table_catalog, table_schema, TABLE_NAME, COLUMN_NAME, ordinal_position, column_comment,"
                        + " " + "data_type, character_maximum_length\n" + "  from INFORMATION_SCHEMA.columns where " +
                        "table_schema = '" + table_schema + "' and table_name" + " " + "= '" + table_name + "' order "
                        + "by"
                        + " " + "ordinal_position";
                ResultSet rs = DBSql.querySql(dbConn, sql);
                datas = getResultSet(rs);
            }

            SqlResponseResult result = new SqlResponseResult();
            result.setDatas(datas);
            return setSuccessBodyByResult(result);
        } finally {
            closeDb(dbConn);
        }
    }

    public static String reqModelConfig_getLimit(JSONObject bodyJson) throws Exception {
        Connection metaConn = null;
        try {
            String sql = "select id, save_row, create_table, user_name, show_row from mxgc_user_config";
            metaConn = MetaMysql.connect();
            ResultSet rs = DBSql.querySql(metaConn, sql);
            ArrayList<Object> datas = getResultSet(rs);
            SqlResponseResult result = new SqlResponseResult();
            result.setDatas(datas);
            return setSuccessBodyByResult(result);
        } finally {
            closeDb(metaConn);
        }
    }

    public static String reqModelConfig_getSchema(JSONObject bodyJson) throws Exception {
        Connection metaConn = null;
        try {
            String sql = "select id, db_name, db_comment, user_name, is_write, show_order, db_type from " +
                    "mxgc_user_dbs" + " " + "order by show_order + 0";
            metaConn = MetaMysql.connect();
            ResultSet rs = DBSql.querySql(metaConn, sql);
            ArrayList<Object> datas = getResultSet(rs);
            SqlResponseResult result = new SqlResponseResult();
            result.setDatas(datas);
            return setSuccessBodyByResult(result);
        } finally {
            closeDb(metaConn);
        }
    }

    public static String reqModelConfig_getTable(JSONObject bodyJson) throws Exception {
        Connection metaConn = null;
        try {
            String db_name = bodyJson.getString("db_name");
            String sql =
                    "select id, user_name, db_name, table_name, table_comment, show_order from mxgc_user_table " +
                            "where " + "db_name = '" + db_name + "' and (mxid is null or length(mxid) = 0) order by " +
                            "show_order + 0";
            metaConn = MetaMysql.connect();
            ResultSet rs = DBSql.querySql(metaConn, sql);
            ArrayList<Object> datas = getResultSet(rs);
            SqlResponseResult result = new SqlResponseResult();
            result.setDatas(datas);
            return setSuccessBodyByResult(result);
        } finally {
            closeDb(metaConn);
        }
    }

    public static String reqEditTableConfig(JSONObject bodyJson) throws Exception {
        Connection metaConn = null;
        try {
            String table_schema = bodyJson.getString("table_schema");
            metaConn = MetaMysql.connect();
            ArrayList<String> sqls = new ArrayList<>();

            String sql =
                    "delete from mxgc_user_table where db_name = '" + table_schema + "' and (mxid is null or " +
                            "length" +
                            "(mxid) = 0)";
            sqls.add(sql);

            JSONArray ja = bodyJson.getJSONArray("data");

            for (int i = 0; i < ja.size(); i++) {
                JSONObject jo = ja.getJSONObject(i);
                String id = jo.getString("id");
                String user_name = jo.getString("user_name");
                String db_name = jo.getString("db_name");
                String table_name = jo.getString("table_name");
                String table_comment = jo.getString("table_comment");
                String show_order = jo.getString("show_order");
                sql = "insert into mxgc_user_table(id, user_name, db_name, table_name, table_comment, " +
                        "table_type, show_order) values ('" + id + "', '" + user_name + "', '" + db_name + "', '" + table_name + "', '" + table_comment + "', '0', '" + show_order + "')";
                sqls.add(sql);
            }
            MetaMysql.batchExecSql(metaConn, sqls);

            CommonResponseBody commonResp = new CommonResponseBody();
            commonResp.setSuccess();
            String respJson = resultSuccess(commonResp);
            return respJson;
        } finally {
            closeDb(metaConn);
        }
    }

    public static String reqModelConfig_getColumn(JSONObject bodyJson) throws Exception {
        Connection metaConn = null;
        try {
            String db_name = bodyJson.getString("db_name");
            String table_name = bodyJson.getString("table_name");
            String sql = "select db_name, table_name, column_name, column_comment, ordinal_position, data_type " +
                    "from mxgc_user_column where db_name = '" + db_name + "' and table_name = '" + table_name + "' " + "order"
                    + " by " + "ordinal_position + 0;";
            metaConn = MetaMysql.connect();
            ResultSet rs = DBSql.querySql(metaConn, sql);
            ArrayList<Object> datas = getResultSet(rs);
            SqlResponseResult result = new SqlResponseResult();
            result.setDatas(datas);
            return setSuccessBodyByResult(result);
        } finally {
            closeDb(metaConn);
        }
    }

    public static String setSuccessBodyByResult(SqlResponseResult result) {
        CommonResponseBody commonResp = new CommonResponseBody();
        commonResp.setSuccess();
        SqlResponseBody body = new SqlResponseBody();
        body.setCommon(commonResp);
        body.setResult(result);
        String respJson = JSONObject.toJSONString(body);
        return respJson;
    }

    public static String reqGetTrainModel(JSONObject bodyJson) throws Exception {
        Connection aiConn = null;
        try {


            aiConn = AISql.connect();
            String sql = bodyJson.getString("sql");
            ResultSet rs = AISql.querySql(aiConn, sql);
            ResultSetMetaData rsd = rs.getMetaData();
            String value = "";
            while (rs.next()) {
                for (int i = 1; i <= rsd.getColumnCount(); i++) {
                    value = value + rs.getString(i) + ";";
                }
                value = value.substring(0, value.length() - 1);
                break;
            }
            CommonResponseBody commonResp = new CommonResponseBody();
            commonResp.setExtraStr(value);
            commonResp.setSuccess();
            String respJson = resultSuccess(commonResp);
            return respJson;
        } finally {
            closeDb(aiConn);
        }
    }


    public static String reqTaskExecute(JSONObject bodyJson) throws Exception {
        Connection metaConn = null;
        Connection dbConn = null;
        try {
            dbConn = DBSql.connect();
            metaConn = MetaMysql.connect();
            String respJson = "";
            String uuid = getUUID();

            String taskType = bodyJson.getString("taskType");
            if (taskType == null) {
                taskType = "model";
            }
            if (taskType.equals("model")) {
                //模型任务
                TaskExecute taskExecute = new TaskExecute(bodyJson);
                new Thread(taskExecute).start();
            } else {
                //图谱任务
                TaskGraphExecute taskGraphExecute = new TaskGraphExecute(bodyJson, uuid);
                new Thread(taskGraphExecute).start();
            }
            CommonRwResponseBody rw = new CommonRwResponseBody();
            rw.setSuccess();
            SqlResponseBody body = new SqlResponseBody();
            CommonResponseBody common = new CommonResponseBody();
            common.setSuccess();
            body.setCommon(common);
            body.setRw(rw);
            respJson = JSONObject.toJSONString(body);
            return respJson;
        } finally {
            closeDb(dbConn);
            closeDb(metaConn);
        }
    }


    public static String reqUpdatePassword(SqlRequestBody sqlReq) throws Exception {
        Connection metaConn = null;
        try {
            long beginTime = Calendar.getInstance().getTimeInMillis();
            metaConn = MetaMysql.connect();
            String password = sqlReq.getPassword();
            String key = sqlReq.getUserName();
            while (key.length() < 8) {
                key = key + "0";
            }
            password = EncryptUtils.encrypt(password, key);
            String sql =
                    "update sys_user set password = '" + password + "' where login_name = '" + sqlReq.getUserName() + "'";
            MetaMysql.execSql(metaConn, sql);
            CommonResponseBody resp = getCommonResponseBody(beginTime);
            String respJson = resultSuccess(resp);
            return respJson;
        } finally {
            closeDb(metaConn);
        }
    }

    public static String reqNeo4jConnect(SqlRequestBody sqlReq) throws Exception {
        Connection metaConn = null;
        try {
            metaConn = MetaMysql.connect();
            String url = globalProperties.getProperty("neo4j.url");
            String username = globalProperties.getProperty("neo4j.user");
            String password = globalProperties.getProperty("neo4j.password");
            String sql = "select '" + url + "' as url, '" + username + "' as username, '" + password + "' as password";
            ResultSet rs = MetaMysql.querySql(metaConn, sql);
            ArrayList<Object> datas = getResultSet(rs);
            SqlResponseResult result = new SqlResponseResult();
            result.setDatas(datas);
            return setSuccessBodyByResult(result);
        } finally {
            closeDb(metaConn);
        }
    }

    public static String reqGetColumnsByList(JSONObject bodyJson) throws Exception {
        Connection metaConn = null;
        try {
            metaConn = MetaMysql.connect();
            String table_name = bodyJson.getString("table_name");
            String sql = "select column_name, CONCAT_WS('|',column_comment,column_name) column_comment " + "from " +
                    "mxgc_user_column where CONCAT_WS('.',db_name,table_name) = '" + table_name + "'";
            ResultSet rs = MetaMysql.querySql(metaConn, sql);
            ArrayList<Object> datas = getResultSet(rs);
            SqlResponseResult result = new SqlResponseResult();
            result.setDatas(datas);
            return setSuccessBodyByResult(result);
        } finally {
            closeDb(metaConn);
        }
    }

    public static String reqNodeBaseQuery(SqlRequestBody sqlReq) throws Exception {
        Connection metaConn = null;
        try {
            metaConn = MetaMysql.connect();
            String sql = "select id, node_e_name, node_c_name, node_color, node_id_field, node_name_field," +
                    "node_icon, node_order, node_type " + "from graph_node_base order by node_order";
            ResultSet rs = DBSql.querySql(metaConn, sql);
            ArrayList<Object> datas = getResultSet(rs);
            SqlResponseResult result = new SqlResponseResult();
            result.setDatas(datas);
            return setSuccessBodyByResult(result);
        } finally {
            closeDb(metaConn);
        }
    }

    public static String reqNodeBaseSave(JSONObject bodyJson) throws Exception {
        Connection metaConn = null;
        try {
            long beginTime = Calendar.getInstance().getTimeInMillis();
            metaConn = MetaMysql.connect();
            JSONObject data = bodyJson.getJSONObject("data");
            String id = data.getString("id");
            String node_e_name = data.getString("node_e_name");
            String node_c_name = data.getString("node_c_name");
            String node_color = data.getString("node_color");
            String node_id_field = data.getString("node_id_field");
            String node_name_field = data.getString("node_name_field");
            String node_icon = data.getString("node_icon");
            String node_order = data.getString("node_order");
            String node_type = data.getString("node_type");

            String sql = "delete from graph_node_base where id = '" + id + "'";
            MetaMysql.execSql(metaConn, sql);
            sql = "insert into graph_node_base(id, node_e_name, node_c_name, node_color, node_id_field, " +
                    "node_name_field, node_icon, node_order, node_type) values('" + id + "','" + node_e_name + "','" + node_c_name + "','" + node_color + "','" + node_id_field + "','" + node_name_field + "','" + node_icon + "','" + node_order + "','" + node_type + "')";
            MetaMysql.execSql(metaConn, sql);
            CommonResponseBody resp = getCommonResponseBody(beginTime);
            String respJson = resultSuccess(resp);
            return respJson;
        } finally {
            closeDb(metaConn);
        }
    }

    public static String reqNodeBaseDelete(JSONObject bodyJson) throws Exception {
        Connection metaConn = null;
        try {
            long beginTime = Calendar.getInstance().getTimeInMillis();
            metaConn = MetaMysql.connect();
            String id = bodyJson.getString("id");
            String sql = "delete from graph_node_base where id = '" + id + "'";
            MetaMysql.execSql(metaConn, sql);

            CommonResponseBody resp = getCommonResponseBody(beginTime);
            String respJson = resultSuccess(resp);
            return respJson;
        } finally {
            closeDb(metaConn);
        }
    }

    public static String reqNodeRelationQuery(SqlRequestBody sqlReq) throws Exception {
        Connection metaConn = null;
        try {
            metaConn = MetaMysql.connect();
            String sql = "select id, table_name, source_node_type, target_node_type, source_node_id_field, " +
                    "target_node_id_field, source_node_name_field, target_node_name_field, relation_field, " +
                    "source_node_attribute, target_node_attribute,graph_node_comment,user_name from " +
                    "graph_node_relation";
            ResultSet rs = DBSql.querySql(metaConn, sql);
            ArrayList<Object> datas = getResultSet(rs);
            SqlResponseResult result = new SqlResponseResult();
            result.setDatas(datas);
            return setSuccessBodyByResult(result);
        } finally {
            closeDb(metaConn);
        }
    }

    public static String reqScreenThemeQuery(SqlRequestBody sqlReq) throws Exception {
        Connection metaConn = null;
        try {
            metaConn = MetaMysql.connect();
            String sql = "select id, ename, cname, screen_color, table_theme, chart_theme from chart_screen_theme";
            ResultSet rs = DBSql.querySql(metaConn, sql);
            ArrayList<Object> datas = getResultSet(rs);
            SqlResponseResult result = new SqlResponseResult();
            result.setDatas(datas);
            return setSuccessBodyByResult(result);
        } finally {
            closeDb(metaConn);
        }
    }

    public static String reqNodeRelationSave(JSONObject bodyJson) throws Exception {
        Connection metaConn = null;
        try {
            long beginTime = Calendar.getInstance().getTimeInMillis();
            metaConn = MetaMysql.connect();
            JSONObject data = bodyJson.getJSONObject("data");
            String id = data.getString("id");
            String table_name = data.getString("table_name");
            String source_node_type = data.getString("source_node_type");
            String target_node_type = data.getString("target_node_type");
            String source_node_id_field = data.getString("source_node_id_field");
            String target_node_id_field = data.getString("target_node_id_field");
            String source_node_name_field = data.getString("source_node_name_field");
            String target_node_name_field = data.getString("target_node_name_field");
            String relation_field = data.getString("relation_field");
            String source_node_attribute = data.getString("source_node_attribute");
            String target_node_attribute = data.getString("target_node_attribute");
            String graph_node_comment = data.getString("graph_node_comment");
            String user_name = data.getString("user_name");
            String sql = "delete from graph_node_relation where id = '" + id + "'";
            MetaMysql.execSql(metaConn, sql);
            sql = "insert into graph_node_relation(id, table_name, source_node_type, target_node_type, " +
                    "source_node_id_field, target_node_id_field, source_node_name_field, target_node_name_field, " +
                    "relation_field, source_node_attribute, target_node_attribute,graph_node_comment, user_name) " +
                    "values "
                    + "('" + id + "', '" + table_name + "', '" + source_node_type + "', '" + target_node_type + "', " + "'" + source_node_id_field + "', '" + target_node_id_field + "', '" + source_node_name_field + "', '" + target_node_name_field + "', '" + relation_field + "', '" + source_node_attribute + "'," + " '" + target_node_attribute + "','" + graph_node_comment + "','" + user_name + "')";
            MetaMysql.execSql(metaConn, sql);
            CommonResponseBody resp = getCommonResponseBody(beginTime);
            String respJson = resultSuccess(resp);
            return respJson;
        } finally {
            closeDb(metaConn);
        }
    }

    public static String reqNodeRelationUpdate(JSONObject bodyJson) throws Exception {
        Connection metaConn = null;
        try {
            long beginTime = Calendar.getInstance().getTimeInMillis();
            metaConn = MetaMysql.connect();
            String table_name = bodyJson.getString("table_name");
            Neo4jDB.makeData(table_name);
            CommonResponseBody resp = getCommonResponseBody(beginTime);
            String respJson = resultSuccess(resp);
            return respJson;
        } finally {
            closeDb(metaConn);
        }
    }

    public static String reqNodeRelationIsTask(JSONObject bodyJson) throws Exception {
        Connection metaConn = null;
        try {
            long beginTime = Calendar.getInstance().getTimeInMillis();
            metaConn = MetaMysql.connect();
            String id = bodyJson.getString("id");
            String taskId = getUUID();
            String sql = "";
            String graph_node_comment = "";
            String taskName = "";
            sql = "select * from graph_node_relation where id=" + id;
            ResultSet rs = MetaMysql.querySql(metaConn, sql);
            if (rs.next()) {
                graph_node_comment = rs.getString("graph_node_comment");
            }
            taskName = graph_node_comment + "_任务";
            sql = "select * from mxgc_user_task where mx_id=" + id;
            rs = MetaMysql.querySql(metaConn, sql);
            if (rs.next()) {
            } else {
                sql =
                        "insert into mxgc_user_task(task_id, task_name, mx_id, status) values('" + taskId + "', '" + taskName + "', '" + id + "', 'run')";
                MetaMysql.execSql(metaConn, sql);
            }
            CommonResponseBody resp = getCommonResponseBody(beginTime);
            String respJson = resultSuccess(resp);
            return respJson;
        } finally {
            closeDb(metaConn);
        }
    }


    public static String reqRoleQuery(SqlRequestBody sqlReq) throws Exception {
        Connection metaConn = null;
        try {
            metaConn = MetaMysql.connect();
            String sql = "select role_id, role_name, role_key from sys_role";
            ResultSet rs = MetaMysql.querySql(metaConn, sql);
            ArrayList<Object> datas = getResultSet(rs);
            SqlResponseResult result = new SqlResponseResult();
            result.setDatas(datas);
            return setSuccessBodyByResult(result);
        } finally {
            closeDb(metaConn);
        }
    }

    public static String reqRoleSave(JSONObject bodyJson) throws Exception {
        Connection metaConn = null;
        try {
            long beginTime = Calendar.getInstance().getTimeInMillis();
            metaConn = MetaMysql.connect();
            JSONObject data = bodyJson.getJSONObject("data");
            String role_id = data.getString("role_id");
            String role_name = data.getString("role_name");
            String role_key = data.getString("role_key");
            String sql = "delete from sys_role where role_id = '" + role_id + "'";
            MetaMysql.execSql(metaConn, sql);

            sql =
                    "insert into sys_role(role_id, role_name, role_key, role_sort, status) values('" + role_id + "', "
                            + "'" + role_name + "', '" + role_key + "', '0', '0')";
            MetaMysql.execSql(metaConn, sql);

            CommonResponseBody resp = getCommonResponseBody(beginTime);
            String respJson = resultSuccess(resp);
            return respJson;
        } finally {
            closeDb(metaConn);
        }
    }


    public static String reqRoleDelete(JSONObject bodyJson) throws Exception {
        Connection metaConn = null;
        try {
            long beginTime = Calendar.getInstance().getTimeInMillis();
            metaConn = MetaMysql.connect();
            String role_id = bodyJson.getString("id");
            String sql = "delete from sys_role where role_id = '" + role_id + "'";
            MetaMysql.execSql(metaConn, sql);
            sql = "delete from sys_user_role where role_id = '" + role_id + "'";
            MetaMysql.execSql(metaConn, sql);


            CommonResponseBody resp = getCommonResponseBody(beginTime);
            String respJson = resultSuccess(resp);
            return respJson;
        } finally {
            closeDb(metaConn);
        }
    }


    public static String reqUserSave(JSONObject bodyJson) throws Exception {
        Connection metaConn = null;
        try {
            long beginTime = Calendar.getInstance().getTimeInMillis();
            metaConn = MetaMysql.connect();
            JSONObject data = bodyJson.getJSONObject("data");
            String login_name = data.getString("login_name");
            String user_name = data.getString("user_name");
            String dept_id = data.getString("dept_id");
            String create_time = data.getString("create_time");
            String id = data.getString("id");
            String password = data.getString("password");
            JSONArray keys = data.getJSONArray("role_key");
            String sql = "delete from sys_user where user_id = '" + id + "'";
            MetaMysql.execSql(metaConn, sql);
            sql = "delete from sys_user_role where user_id = '" + id + "'";
            MetaMysql.execSql(metaConn, sql);

            sql = "insert into sys_user(user_id, login_name, user_name, password, dept_id, " +
                    "create_time) values('" + id + "', '" + login_name + "', '" + user_name + "', '"
                    + password + "', '" + dept_id + "', '" + create_time + "')";
            MetaMysql.execSql(metaConn, sql);
            for (int i = 0; i < keys.size(); i++) {
                String key = keys.getString(i);
                sql = "insert into sys_user_role(user_id, role_id) select '" + id + "', role_id from sys_role " +
                        "where role_key = '" + key + "'";
                MetaMysql.execSql(metaConn, sql);
            }

            if (password.length() == 0) {
                //设置默认密码
                SqlRequestBody sqlReq = new SqlRequestBody();
                sqlReq.setPassword("123456");
                sqlReq.setUserName(login_name);
                reqUpdatePassword(sqlReq);
            }

            CommonResponseBody resp = getCommonResponseBody(beginTime);
            String respJson = resultSuccess(resp);
            return respJson;
        } finally {
            closeDb(metaConn);
        }
    }


    public static String reqUserDelete(JSONObject bodyJson) throws Exception {
        Connection metaConn = null;
        try {
            long beginTime = Calendar.getInstance().getTimeInMillis();
            metaConn = MetaMysql.connect();
            String id = bodyJson.getString("id");
            String sql = "delete from sys_user where user_id = '" + id + "'";
            MetaMysql.execSql(metaConn, sql);
            sql = "delete from sys_user_role where user_id = '" + id + "'";
            MetaMysql.execSql(metaConn, sql);

            CommonResponseBody resp = getCommonResponseBody(beginTime);
            String respJson = resultSuccess(resp);
            return respJson;
        } finally {
            closeDb(metaConn);
        }
    }


    public static String reqCreateTask(SqlRequestBody sqlReq) throws Exception {
        Connection dbConn = null;
        Connection metaConn = null;
        try {
            long beginTime = Calendar.getInstance().getTimeInMillis();
            dbConn = DBSql.connect();
            metaConn = MetaMysql.connect();
            String taskId = getUUID();
            String taskName = sqlReq.getMxmc() + "_任务";
            String sql = "select * from mxgc_user_task where mx_id ='" + sqlReq.getMxid() + "'";
            ResultSet rs = MetaMysql.querySql(metaConn, sql);
            if (rs.next()) {
                sql =
                        "update mxgc_user_task set task_name = '" + taskName + "', status = 'run' where mx_id = '" + sqlReq.getMxid() + "'";
                MetaMysql.execSql(metaConn, sql);
            } else {
                sql =
                        "insert into mxgc_user_task(task_id, task_name, mx_id, status) values('" + taskId + "', '" + taskName + "', '" + sqlReq.getMxid() + "', 'run')";
                MetaMysql.execSql(metaConn, sql);
            }
            CommonResponseBody resp = getCommonResponseBody(beginTime);
            String respJson = resultSuccess(resp);
            return respJson;
        } finally {
            closeDb(dbConn);
            closeDb(metaConn);
        }
    }

    public static String reqTaskStatus(SqlRequestBody sqlReq, String status) throws Exception {
        Connection metaConn = null;
        try {
            long beginTime = Calendar.getInstance().getTimeInMillis();
            metaConn = MetaMysql.connect();
            String sql = "update mxgc_user_task set status = '" + status + "' where mx_id='" + sqlReq.getMxid() + "'";
            MetaMysql.execSql(metaConn, sql);
            CommonResponseBody resp = getCommonResponseBody(beginTime);
            String respJson = resultSuccess(resp);
            return respJson;
        } finally {
            closeDb(metaConn);
        }
    }

    private static String getMyDbName(String tableName) {
        String[] tmpstr = tableName.split("\\.");
        return tmpstr[0];
    }


    public static String reqShareOperate(SqlRequestBody sqlReq, String status) throws Exception {

        Connection dbConn = null;
        Connection metaConn = null;
        try {
            long beginTime = Calendar.getInstance().getTimeInMillis();
            dbConn = DBSql.connect();
            metaConn = MetaMysql.connect();
            //状态；0: 代表申请； 1：代表通过 2:取消
            String sql =
                    "update mxgc_user_mx_gx a set status = '" + status + "' where a.id = '" + sqlReq.getApplyId() +
                            "' and " + "a" + ".mx_id = '" + sqlReq.getMxid() + "'";
            if (status.equals("2")) {
                sql =
                        "delete from mxgc_user_mx_gx  where id = '" + sqlReq.getApplyId() + "' and mx_id = '" + sqlReq.getMxid() + "'";

            }
            MetaMysql.execSql(metaConn, sql);
            String applyType = sqlReq.getApplyType();
            String mlid = sqlReq.getMlid();
            String standardSql = "";
            if (status.equals("1")) {
                //同意共享
                if (applyType.equals("0")) {
                    //模型共享
                    sql =
                            "update mxgc_user_mxlb b set mxlx = '0', mlid = '" + mlid + "' where b.id = '" + sqlReq.getMxid() + "'";
                    standardSql =
                            "update standard_table set is_share = 'pass', mlid = '" + mlid + "' where bz1 = '" + sqlReq.getMxid() + "'";
                }
                if (applyType.equals("2")) {
                    //分析共享
                    sql =
                            "update chart_component_list b set if_publish = '0', mlid = '" + mlid + "' where b.id = " + "'" + sqlReq.getMxid() + "'";
                }
                if (applyType.equals("3")) {
                    //看板共享
                    sql = "update chart_screen_list b set if_publish = '0', mlid = '" + mlid + "' where " + "b" +
                            ".screen_id" + " " + "=" + " '" + sqlReq.getMxid() + "'";
                }

            } else {
                //取消共享
                if (applyType.equals("0")) {
                    sql = "update mxgc_user_mxlb b set mxlx = '1', mlid = '100' where b.id = '" + sqlReq.getMxid() +
                            "'";
                    standardSql =
                            "update standard_table set is_share = '', mlid = '100' where bz1 = '" + sqlReq.getMxid() +
                                    "'";
                }
                if (applyType.equals("2")) {
                    sql =
                            "update chart_component_list b set if_publish = '1', mlid = '500' where b.id = '" + sqlReq.getMxid() + "'";
                }
                if (applyType.equals("3")) {
                    sql =
                            "update chart_screen_list b set if_publish = '1', mlid = '700' where b.screen_id = '" + sqlReq.getMxid() + "'";
                }
            }
            MetaMysql.execSql(metaConn, sql);
            if (standardSql.length() > 0) {
                MetaMysql.execSql(metaConn, standardSql);
            }

            //下面这一段对模型有用
            if (applyType.equals("0")) {
                sql = "select input_tables from mxgc_user_table where  mxid = '" + sqlReq.getMxid() + "'";
                ResultSet rs = MetaMysql.querySql(metaConn, sql);
                if (rs.next()) {
                    ArrayList<Object> dbs = getMyDefaultDbName(sqlReq);
                    String[] inputTables = rs.getString("input_tables").split(",");
                    for (int i = 0; i < inputTables.length; i++) {
                        String one = inputTables[i];
                        boolean isExist = false;
                        for (int j = 0; j < dbs.size(); j++) {
                            Object db = dbs.get(j);
                            String value = getDynamicObjectValue(db, 0);
                            if (one.startsWith(value + ".")) {
                                isExist = true;
                                break;
                            }
                        }
                        if (isExist) {
                            if (status.equals("1")) {
                                //同意共享
                                sql = "update mxgc_user_mxlb set mxlx = '0', mlid = '" + mlid + "' where id in " +
                                        "(select mxid from mxgc_user_table " + "where concat_ws('.', db_name, " +
                                        "table_name) = " +
                                        "'" + one + "')";
                                standardSql = "update standard_table set is_share = 'pass', mlid = '" + mlid + "' " +
                                        "where bz1" + " " + "in (select mxid from " + "mxgc_user_table where " +
                                        "concat_ws('.', " +
                                        "db_name, table_name) = '" + one + "')";
                            } else {
                                //取消共享
                                sql = "update mxgc_user_mxlb set mxlx = '1', mlid = '100" + "' where id in (select " +
                                        "mxid from mxgc_user_table " + "where concat_ws('.', db_name, table_name) = " +
                                        "'" + one + "')";
                                standardSql = "update standard_table set is_share = '', mlid = '100' where bz1 " +
                                        "in (select mxid from " + "mxgc_user_table where concat_ws('.', db_name, " +
                                        "table_name) " +
                                        "= '" + one + "')";
                            }
                            MetaMysql.execSql(metaConn, sql);
                            MetaMysql.execSql(metaConn, standardSql);
                        }
                    }
                }
            }

            CommonResponseBody resp = getCommonResponseBody(beginTime);
            String respJson = resultSuccess(resp);
            return respJson;
        } finally {
            closeDb(dbConn);
            closeDb(metaConn);
        }
    }


    private static MxResponseBody rsToMxResponseBody(ResultSet rs) throws Exception {
        MxResponseBody mxResponseBody = new MxResponseBody();
        mxResponseBody.setMxId(rs.getString("id"));
        mxResponseBody.setMxmc(rs.getString("mxmc"));
        mxResponseBody.setMxsm(rs.getString("mxsm"));
        mxResponseBody.setMxlx(rs.getString("mxlx"));
        mxResponseBody.setMxtp(rs.getString("mxtp"));
        mxResponseBody.setMxnr(rs.getString("mxnr"));
        mxResponseBody.setMlid(rs.getString("mlid"));
        mxResponseBody.setLangType(rs.getString("lang_type"));
        ResultSetMetaData metaData = rs.getMetaData();
        int columnCount = metaData.getColumnCount();
        ArrayList<String> columns = new ArrayList<String>();
        for (int i = 1; i <= columnCount; i++) {
            columns.add(metaData.getColumnName(i));
        }
        if (columns.indexOf("table_name") >= 0 && rs.getString("table_name") != null) {
            mxResponseBody.setTableName(rs.getString("table_name"));
        }

        return mxResponseBody;
    }

    public static String reqApplyShare(SqlRequestBody sqlReq) throws Exception {

        Connection dbConn = null;
        Connection metaConn = null;
        try {
            long beginTime = Calendar.getInstance().getTimeInMillis();
            dbConn = DBSql.connect();
            metaConn = MetaMysql.connect();
            String id = getUUID();
            String applyType = sqlReq.getApplyType();
            String mxid = sqlReq.getMxid();
            String userName = sqlReq.getUserName();
            String sql = "select mx_id from mxgc_user_mx_gx where mx_id = '" + mxid + "'";
            ResultSet rs = MetaMysql.querySql(metaConn, sql);
            if (rs.next()) {
                CommonResponseBody resp = getCommonResponseBody(beginTime);
                resp.setMsg("已申请，请等待审批结果");
                String respJson = resultSuccess(resp);
                return respJson;
            }
            sql =
                    "insert into mxgc_user_mx_gx(id, mx_id, apply_user, apply_time, status, apply_type) values" +
                            "('" + id + "','" + mxid + "','" + userName + "','" + getNowDate() + "','0'," + "'" + applyType + "')";
            MetaMysql.execSql(metaConn, sql);
            CommonResponseBody resp = getCommonResponseBody(beginTime);
            resp.setMsg("申请成功，请等待审批结果");
            String respJson = resultSuccess(resp);
            return respJson;
        } finally {
            closeDb(dbConn);
            closeDb(metaConn);
        }
    }

    public static String reqGetUserTableLimit(SqlRequestBody sqlReq) throws Exception {
        Connection dbConn = null;
        Connection metaConn = null;
        try {
            long beginTime = Calendar.getInstance().getTimeInMillis();
            dbConn = DBSql.connect();
            metaConn = MetaMysql.connect();
            //得到用户配置
            String sql =
                    "select save_row, show_row, create_table from mxgc_user_config where user_name = '" + sqlReq.getUserName() + "'";
            ResultSet rs_conf = MetaMysql.querySql(metaConn, sql);
            String create_table = "30";
            if (rs_conf.next()) {
                create_table = rs_conf.getString("create_table");
            }
            //判断用户建表的个数是否超过限制
            sql =
                    "select count(1) cun from mxgc_user_table where table_type = '1' and user_name = '" + sqlReq.getUserName() + "'";
            ResultSet rs_table = MetaMysql.querySql(metaConn, sql);
            String table_count = "0";
            if (rs_table.next()) {
                table_count = rs_table.getString("cun");
            }
            if (Integer.parseInt(create_table) <= Integer.parseInt(table_count)) {
                return returnFail("创建表的权限已经达到上限");
            }
            CommonResponseBody resp = getCommonResponseBody(beginTime);
            String respJson = resultSuccess(resp);
            return respJson;
        } finally {
            closeDb(dbConn);
            closeDb(metaConn);
        }

    }

    public static String reqCreateDir(SqlRequestBody sqlReq) throws Exception {
        Connection metaConn = null;
        try {
            metaConn = MetaMysql.connect();
            String dirParentKey = sqlReq.getDirParentKey();
            String dirTitle = sqlReq.getDirTitle();
            String mlid = dirParentKey + "100";
            String userName = sqlReq.getUserName();
            String dirOrder = sqlReq.getDirOrder();

            String sql = "select max(id) + 1 from mxgc_user_mllb where parent_id = '" + dirParentKey + "'";
            ResultSet rs = MetaMysql.querySql(metaConn, sql);
            if (rs.next()) {
                if (rs.getLong(1) > 0) {
                    mlid = String.valueOf(rs.getLong(1));
                }
            }

            sql = "insert into mxgc_user_mllb(id, user_name, mlmc, parent_id, show_order) " + "values('" + mlid + "',"
                    + "'" + userName + "','" + dirTitle + "','" + dirParentKey + "','" + dirOrder + "')";
            MetaMysql.execSql(metaConn, sql);

            ReturnModelDir returnModelDir = setReturnModelDir();
            String respJson = JSONObject.toJSONString(returnModelDir);
            return respJson;
        } finally {
            closeDb(metaConn);
        }
    }

    public static String reqDeleteDir(SqlRequestBody sqlReq) throws Exception {
        Connection metaConn = null;
        String userName = sqlReq.getUserName();
        try {
            metaConn = MetaMysql.connect();
            String mlid = sqlReq.getMlid();

            String sql = "delete from mxgc_user_mllb where id ='" + mlid + "'" + " and user_name = '" + userName + "'";
            MetaMysql.execSql(metaConn, sql);
            //删除表
            if (mlid.startsWith("3") || mlid.startsWith("4")) {
                //标签目录
                sql =
                        "delete from mxgc_user_table " + " where mxid in ( select id from " + " mxgc_user_mxlb where " +
                                "sfbq = '1' and bqmlid ='" + mlid + "'" + " and user_name = '" + userName + "')" + " "
                                + "and " +
                                "user_name " + "=" + " '" + userName + "'";
            } else {
                //模型目录
                sql =
                        "delete from mxgc_user_table " + " where mxid in ( select id from " + " mxgc_user_mxlb where " +
                                "mlid ='" + mlid + "'" + " and user_name = '" + userName + "')" + " and user_name = " + "'" + userName + "'";
            }
            MetaMysql.execSql(metaConn, sql);
            //删除模型
            if (mlid.startsWith("3") || mlid.startsWith("4")) {
                //标签目录
                sql =
                        "delete from mxgc_user_mxlb where sfbq = '1' and bqmlid ='" + mlid + "'" + " and user_name = "
                                + "'" + userName + "'";
            } else {
                //模型目录
                sql = "delete from mxgc_user_mxlb where mlid ='" + mlid + "'" + " and user_name = '" + userName + "'";
            }
            MetaMysql.execSql(metaConn, sql);


            ReturnModelDir returnModelDir = setReturnModelDir();
            String respJson = JSONObject.toJSONString(returnModelDir);
            return respJson;
        } finally {
            closeDb(metaConn);
        }
    }

    public static String reqModifyDir(SqlRequestBody sqlReq) throws Exception {
        Connection metaConn = null;
        String userName = sqlReq.getUserName();
        try {
            metaConn = MetaMysql.connect();
            String mlid = sqlReq.getMlid();
            String dirTitle = sqlReq.getDirTitle();
            String dirOrder = sqlReq.getDirOrder();
            String parentKey = sqlReq.getDirParentKey();

            String sql = "update mxgc_user_mllb set mlmc='" + dirTitle + "', show_order='" + dirOrder + "', parent_id"
                    + " = '" + parentKey + "' where id = '" + mlid + "'" + " and user_name = '" + userName + "'";
            MetaMysql.execSql(metaConn, sql);

            ReturnModelDir returnModelDir = setReturnModelDir();
            String respJson = JSONObject.toJSONString(returnModelDir);
            return respJson;
        } finally {
            closeDb(metaConn);
        }
    }

    public static ReturnModelDir setReturnModelDir() {
        CommonResponseBody common = new CommonResponseBody();
        common.setSuccess();
        ReturnModelDir returnModelDir = new ReturnModelDir();
        returnModelDir.setCommon(common);
        return returnModelDir;
    }


    public static String reqDeleteModel(SqlRequestBody sqlReq) throws Exception {
        Connection metaConn = null;
        Connection dbConn = null;
        String userName = sqlReq.getUserName();
        try {
            metaConn = MetaMysql.connect();
            dbConn = DBSql.connect();
            String mxId = sqlReq.getMxid();

            String sql =
                    "select db_name, table_name from mxgc_user_table " + "where mxid ='" + mxId + "'" + " and " +
                            "user_name"
                            + " = '" + userName + "'";
            ResultSet rs = MetaMysql.querySql(metaConn, sql);
            while (rs.next()) {
                String db_name = rs.getString(1);
                String table_name = rs.getString(2);
                sql =
                        "delete from mxgc_user_column where db_name = '" + db_name + "' and table_name = '" + table_name + "'";
                MetaMysql.execSql(metaConn, sql);
                try {
                    sql = "drop table " + db_name + "." + table_name;
                    DBSql.execSql(dbConn, sql);
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
            sql = "delete from mxgc_user_mxlb " + "where id ='" + mxId + "'" + " and user_name = '" + userName + "'";
            MetaMysql.execSql(metaConn, sql);
            sql = "delete from mxgc_user_table " + "where mxid ='" + mxId + "'" + " and user_name = '" + userName + "'";
            MetaMysql.execSql(metaConn, sql);
            sql = "delete from mxgc_user_task where mx_id = '" + mxId + "'";
            MetaMysql.execSql(metaConn, sql);

            //删除元数据
            sql =
                    "delete from standard_column where table_id in (select id from standard_table where bz1='" + mxId + "')";
            MetaMysql.execSql(metaConn, sql);
            sql = "delete from standard_table where bz1 = '" + mxId + "'";
            MetaMysql.execSql(metaConn, sql);

            //删除共享申请
            sql = "delete from mxgc_user_mx_gx where mx_id = '" + mxId + "'";
            MetaMysql.execSql(metaConn, sql);


            ReturnModelList returnModelList = setReturnModelList();
            String respJson = JSONObject.toJSONString(returnModelList);
            return respJson;
        } finally {
            closeDb(metaConn);
            closeDb(dbConn);
        }
    }

    public static String reqDeleteComponent(SqlRequestBody sqlReq) throws Exception {
        Connection metaConn = null;
        Connection dbConn = null;
        String userName = sqlReq.getUserName();
        try {
            metaConn = MetaMysql.connect();
            dbConn = DBSql.connect();
            String mxId = sqlReq.getMxid();


            String sql =
                    "delete from chart_component_list " + "where id ='" + mxId + "'" + " and user_name = '" + userName + "'";
            MetaMysql.execSql(metaConn, sql);


            ReturnModelList returnModelList = setReturnModelList();
            String respJson = JSONObject.toJSONString(returnModelList);
            return respJson;
        } finally {
            closeDb(metaConn);
            closeDb(dbConn);
        }
    }

    public static String reqDeleteScreen(SqlRequestBody sqlReq) throws Exception {
        Connection metaConn = null;
        Connection dbConn = null;
        String userName = sqlReq.getUserName();
        try {
            metaConn = MetaMysql.connect();
            dbConn = DBSql.connect();
            String mxId = sqlReq.getMxid();


            String sql =
                    "delete from chart_screen_list " + "where screen_id ='" + mxId + "'" + " and user_name = '" + userName + "'";
            MetaMysql.execSql(metaConn, sql);


            ReturnModelList returnModelList = setReturnModelList();
            String respJson = JSONObject.toJSONString(returnModelList);
            return respJson;
        } finally {
            closeDb(metaConn);
            closeDb(dbConn);
        }
    }

    public static ReturnModelList setReturnModelList() {
        CommonResponseBody common = new CommonResponseBody();
        common.setSuccess();
        ReturnModelList returnModelList = new ReturnModelList();
        returnModelList.setCommon(common);
        return returnModelList;
    }

    public static String reqGetModelList(SqlRequestBody sqlReq) throws Exception {
        Connection metaConn = null;
        String userName = sqlReq.getUserName();
        try {
            metaConn = MetaMysql.connect();
            String mlId = sqlReq.getMlid();
            String mxsm = sqlReq.getMxsm();
            String mxmc = sqlReq.getMxmc();
            String sql = "select a.id, a.mxmc, a.mxlx, a.mxsm, a.user_name, a.mxtp, a.mxnr, a.mlid, c.ai_type as " +
                    "lang_type, concat_ws('.', c.db_name, c.table_name) as table_name from mxgc_user_mxlb a " +
                    "left join mxgc_user_mllb b on a.mlid = b.id left join mxgc_user_table c " +
                    "on a.id = c.mxid where a.mlid like '" + mlId + "%'";

            if (!mlId.startsWith("200")) {
                sql = sql + " and ((a.user_name = '" + userName + "') or " +
                        "(EXISTS (SELECT 1 FROM mxgc_assessment_period WHERE teacher_name = '" + userName + "') \n" +
                        "                                    AND a.mlid LIKE '4100%'))";
            }
            if (mxsm.length() > 0 && mxmc.length() <= 0) {
                sql = sql + " and a.mxsm like '%" + mxsm + "%'";
            }
            if (mxmc.length() > 0 && mxsm.length() <= 0) {
                sql = sql + " and a.mxmc like '%" + mxmc + "%'";
            }
            if (mxmc.length() > 0 && mxsm.length() > 0) {
                sql = sql + " and (a.mxmc like '%" + mxmc + "%' or a.mxsm like '%" + mxsm + "%' ) ";
            }
            sql = sql + " order by a.create_time desc";
            ResultSet rs = MetaMysql.querySql(metaConn, sql);

            ArrayList<MxResponseBody> mxResponseBodys = new ArrayList<>();
            while (rs.next()) {
                MxResponseBody mxResponseBody = rsToMxResponseBody(rs);
                mxResponseBodys.add(mxResponseBody);
            }

            ReturnModelList returnModelList = setReturnModelList();

            returnModelList.setMxResponseBodys(mxResponseBodys);
            String respJson = JSONObject.toJSONString(returnModelList);
            return respJson;
        } finally {
            closeDb(metaConn);
        }
    }

    public static String reqGetComponentList(SqlRequestBody sqlReq) throws Exception {
        Connection metaConn = null;
        String userName = sqlReq.getUserName();
        try {
            metaConn = MetaMysql.connect();
            String mlId = sqlReq.getMlid();
            String mxsm = sqlReq.getMxsm();
            String mxmc = sqlReq.getMxmc();
            String sql = "select a.id, a.name as mxmc, a.if_publish as mxlx, a.issue as mxsm, a.user_name, " +
                    "a.chart_config as mxnr, a.mlid, a.chart_image as mxtp, 'sql_' as lang_type " + "from " +
                    "chart_component_list a left join  mxgc_user_mllb b on a.mlid = b.id " +
                    "where a.mlid like '" + mlId +   "%'";
            if (!mlId.startsWith("600")) {
                sql = sql + " and ((a.user_name = '" + userName + "') or " +
                        "(EXISTS (SELECT 1 FROM mxgc_assessment_period WHERE teacher_name = '" + userName + "') \n" +
                        "                                    AND a.mlid LIKE '4100%'))";
            }
            if (mxsm.length() > 0 && mxmc.length() <= 0) {
                sql = sql + " and a.issue like '%" + mxsm + "%'";
            }
            if (mxmc.length() > 0 && mxsm.length() <= 0) {
                sql = sql + " and a.name like '%" + mxmc + "%'";
            }
            if (mxmc.length() > 0 && mxsm.length() > 0) {
                sql = sql + " and (a.name like '%" + mxmc + "%' or a.issue like '%" + mxsm + "%' ) ";
            }
            sql = sql + " order by a.create_time desc";
            ResultSet rs = MetaMysql.querySql(metaConn, sql);

            ArrayList<MxResponseBody> mxResponseBodys = new ArrayList<>();
            while (rs.next()) {
                MxResponseBody mxResponseBody = rsToMxResponseBody(rs);
                mxResponseBodys.add(mxResponseBody);
            }

            ReturnModelList returnModelList = setReturnModelList();
            returnModelList.setMxResponseBodys(mxResponseBodys);
            String respJson = JSONObject.toJSONString(returnModelList);
            return respJson;
        } finally {
            closeDb(metaConn);
        }
    }

    public static String reqGetScreenList(SqlRequestBody sqlReq) throws Exception {
        Connection metaConn = null;
        String userName = sqlReq.getUserName();
        try {
            metaConn = MetaMysql.connect();
            String mlId = sqlReq.getMlid();
            String mxsm = sqlReq.getMxsm();
            String mxmc = sqlReq.getMxmc();
            String sql = "select a.screen_id as id, a.name as mxmc, a.if_publish as mxlx, a.issue as mxsm, " +
                    "a.user_name, a.screen_config as mxnr, a.mlid, a.screen_image as mxtp, 'sql_' as lang_type from "
                    + "chart_screen_list a left join mxgc_user_mllb b on a.mlid = b.id where a.mlid like '" + mlId +
                    "%'";
            if (!mlId.startsWith("800")) {
                sql = sql + " and ((a.user_name = '" + userName + "') or " +
                        "(EXISTS (SELECT 1 FROM mxgc_assessment_period WHERE teacher_name = '" + userName + "') \n" +
                        "                                    AND a.mlid LIKE '4100%'))";
            }
            if (mxsm.length() > 0 && mxmc.length() <= 0) {
                sql = sql + " and a.issue like '%" + mxsm + "%'";
            }
            if (mxmc.length() > 0 && mxsm.length() <= 0) {
                sql = sql + " and a.name like '%" + mxmc + "%'";
            }
            if (mxmc.length() > 0 && mxsm.length() > 0) {
                sql = sql + " and (a.name like '%" + mxmc + "%' or a.issue like '%" + mxsm + "%' ) ";
            }
            sql = sql + " order by a.create_time desc";
            ResultSet rs = MetaMysql.querySql(metaConn, sql);

            ArrayList<MxResponseBody> mxResponseBodys = new ArrayList<>();
            while (rs.next()) {
                MxResponseBody mxResponseBody = rsToMxResponseBody(rs);
                mxResponseBodys.add(mxResponseBody);
            }

            ReturnModelList returnModelList = setReturnModelList();
            returnModelList.setMxResponseBodys(mxResponseBodys);
            String respJson = JSONObject.toJSONString(returnModelList);
            return respJson;
        } finally {
            closeDb(metaConn);
        }
    }


    public static String getModelDir(SqlRequestBody sqlReq) throws Exception {
        Connection metaConn = null;
        String userName = sqlReq.getUserName();
        try {
            metaConn = MetaMysql.connect();
            String mlid = sqlReq.getMlid();
            String sql = "select a.id, a.user_name, a.mlmc, a.parent_id, a.show_order, b.mlmc as parent_mc" + " from "
                    + "mxgc_user_mllb a " + "left join mxgc_user_mllb b  on a.parent_id = b.id where a.id in (" + mlid + ") "
                    + "order by a.parent_id + 0, a.show_order + 0";
            ResultSet rs = MetaMysql.querySql(metaConn, sql);
            ArrayList<TreeResult> treeResults = new ArrayList<>();
            while (rs.next()) {
                treeResults.add(resultSetToTreeResult(metaConn, rs, userName));
            }
            //考核目录
            sql = "select dir_code, dir_name from mxgc_assessment_period order by update_time";
            rs = MetaMysql.querySql(metaConn, sql);
            int i = 1;
            TreeResult tr_kh = new TreeResult();
            tr_kh.setKey("4100");
            tr_kh.setValue("4100");
            tr_kh.setTitle("考核目录");
            ArrayList<Object> tr_kh_children = new ArrayList<>();
            while (rs.next()) {
                TreeResult tr = new TreeResult();
                tr.setKey(rs.getString("dir_code"));
                tr.setValue(rs.getString("dir_code"));
                tr.setTitle(rs.getString("dir_name"));
                tr.setShowOrder(String.valueOf(i));
                tr.setParentId("4100");
                tr.setParentMc("考核目录");
                tr_kh_children.add(tr);
            }
            tr_kh.setChildren(tr_kh_children);
            if (tr_kh_children.size() > 0){
                treeResults.add(tr_kh);
            }
            ReturnModelDir returnModelDir = setReturnModelDir();
            returnModelDir.setTreeResults(treeResults);
            String respJson = JSONObject.toJSONString(returnModelDir);
            return respJson;
        } finally {
            closeDb(metaConn);
        }
    }


    public static TreeResult resultSetToTreeResult(Connection metaConn, ResultSet rs, String userName) throws Exception {
        TreeResult tr = new TreeResult();
        tr.setKey(rs.getString("id"));
        tr.setValue(rs.getString("id"));
        tr.setTitle(rs.getString("mlmc"));
        tr.setShowOrder(rs.getString("show_order"));
        tr.setParentId(rs.getString("parent_id"));
        tr.setParentMc(rs.getString("parent_mc"));
        tr.setChildren(getModelDirSub(metaConn, userName, rs.getString("id")));
        return tr;
    }

    public static ArrayList<Object> getModelDirSub(Connection metaConn, String userName, String parentId) throws Exception {
        String sql = "select a.id, a.user_name, a.mlmc, a.parent_id, a.show_order, b.mlmc as parent_mc from " +
                "mxgc_user_mllb" + " a " + "left join mxgc_user_mllb b  on a.parent_id = b.id  where a.parent_id = '" + parentId + "'";
        if (!(parentId.startsWith("200") || parentId.startsWith("400") || parentId.startsWith("600") || parentId.startsWith("800"))) {
            //非共享时，加入用户条件
            sql = sql + " and a.user_name = '" + userName + "'";

        }
        sql = sql + " order by a.parent_id + 0, a.show_order + 0";
        ResultSet rs = MetaMysql.querySql(metaConn, sql);
        ArrayList<Object> children = new ArrayList<>();
        while (rs.next()) {
            children.add(resultSetToTreeResult(metaConn, rs, userName));
        }
        return children;
    }


    public static String reqCheckFunc(SqlRequestBody sqlReq) throws Exception {
        CommonResponseBody resp = new CommonResponseBody();
        resp.setSuccess();
        Connection dbConn = null;
        try {
            dbConn = DBSql.connect();
            String sql = sqlReq.getSql();
            sql = "select " + sql;
            if (DBSql.dbType.equals("oracle")) {
                sql = sql + " from dual";
            }
            ResultSet rs = DBSql.querySql(dbConn, sql);
            if (rs.next()) {
                resp.setExtraStr(rs.getString(1));
            } else {
                resp.setExtraStr("");
            }
            String respJson = resultSuccess(resp);
            return respJson;
        } finally {
            closeDb(dbConn);
        }
    }


    public static String reqMakeToken(SqlRequestBody sqlReq) throws Exception {
        CommonResponseBody resp = new CommonResponseBody();
        resp.setSuccess();
        Connection metaConn = null;
        try {
            metaConn = MetaMysql.connect();
            String userName = sqlReq.getUserName();
            String password = sqlReq.getPassword();
            try {
                String key = sqlReq.getUserName();
                while (key.length() < 8) {
                    key = key + "0";
                }
                password = EncryptUtils.encrypt(password, key);
            } catch (Exception ex) {

            }
            String sql =
                    "select user_name from sys_user where password = '" + password + "' and login_name = '" + userName + "'";
            ResultSet rs = MetaMysql.querySql(metaConn, sql);
            if (rs.next()) {
                resp.setExtraStr(encryptToken(userName));
            } else {
                resp.setExtraStr("");
            }
            String respJson = resultSuccess(resp);
            return respJson;
        } finally {
            closeDb(metaConn);
        }
    }

    public static String encryptToken(String userName) throws Exception {
        String key = "jmpt" + getNowDate().substring(0, 10);
        if (JettyServer.encrypt.equals("other")) {
            key = MyDateUtils.getNowDateBySimple().substring(0, 8);
        }
        try {
            return EncryptUtils.encrypt(userName, key);
        } catch (Exception ex) {
            logger.info(ex.getMessage());
            ex.printStackTrace();
        }
        return "";
    }

    public static String decryptToken(String token) {
        String key = "jmpt" + getNowDate().substring(0, 10);
        if (JettyServer.encrypt.equals("other")) {
            key = MyDateUtils.getNowDateBySimple().substring(0, 8);
        }
        try {
            return EncryptUtils.decrypt(token, key);
        } catch (Exception ex) {
            logger.info(ex.getMessage());
            ex.printStackTrace();
        }
        return "";
    }

    public static String reqLogin(SqlRequestBody sqlReq) throws Exception {
        Connection metaConn = null;
        CommonResponseBody resp = new CommonResponseBody();
        resp.setSuccess();
        try {
            metaConn = MetaMysql.connect();
            String uuid = getUUID();
            if (sqlReq.getMxid() == null) {
                sqlReq.setMxid(getUUID());
            }
            String userName = sqlReq.getUserName();
            userName = decryptToken(userName);
            sqlReq.setUserName(userName);
            Thread.sleep(1000);
            String sql =
                    "insert into mxgc_user_login_log(id, user_name, mx_id) " + "values('" + uuid + "','" + userName + "','" + sqlReq.getMxid() + "')";
            MetaMysql.execSql(metaConn, sql);
            resp.setExtraStr(uuid);
            String respJson = resultSuccess(resp);
            return respJson;
        } finally {
            closeDb(metaConn);
        }
    }

    public static String reqScreenOperateLog(SqlRequestBody sqlReq) throws Exception {
        SqlResponseBody resp = new SqlResponseBody();
        long beginTime = Calendar.getInstance().getTimeInMillis();
        resp.setCommon(getCommonResponseBody(beginTime));
        String respJson = JSONObject.toJSONString(resp);
        return respJson;
    }

    public static String reqOperateLog(SqlRequestBody sqlReq) throws Exception {
        Connection metaConn = null;
        CommonResponseBody resp = new CommonResponseBody();
        resp.setSuccess();
        try {
            metaConn = MetaMysql.connect();
            String uuid = getUUID();
            String userName = sqlReq.getUserName();
            //userName = decryptToken(userName);
            String operateTime = getNowDate();
            String operateType = sqlReq.getOperateType();
            String sql =
                    "insert into mxgc_user_operate_log(id, user_name, operate_time, operate_type) " + "values('" + uuid +
                            "','" + userName + "','" + operateTime + "','" + operateType + "')";
            MetaMysql.execSql(metaConn, sql);
            String respJson = JSONObject.toJSONString(resp);
            return respJson;
        } finally {
            closeDb(metaConn);
        }
    }


    public static String reqGetScreenImages(SqlRequestBody sqlReq) throws Exception {
        Connection metaConn = null;
        try {
            metaConn = MetaMysql.connect();
            String sql =
                    "select id, name, content from chart_screen_images where user_name = '" + sqlReq.getUserName() +
                            "' or " + "user_name = 'mxgc_common' order by update_time";
            if (!sqlReq.getBz().equals("all")) {
                sql = sql.replaceAll("'mxgc_common'", "''");
            }
            ResultSet rs = MetaMysql.querySql(metaConn, sql);
            ArrayList<Object> datas = getResultSet(rs);
            SqlResponseResult result = new SqlResponseResult();
            result.setDatas(datas);
            SqlResponseBody resp = new SqlResponseBody();
            long beginTime = Calendar.getInstance().getTimeInMillis();
            resp.setCommon(getCommonResponseBody(beginTime));
            resp.setResult(result);
            String respJson = JSONObject.toJSONString(resp);
            return respJson;
        } finally {
            closeDb(metaConn);
        }
    }

    public static String getTableCommon(ArrayList<Object> tables, String find_table_name) throws Exception {
        for (int i = 0; i < tables.size(); i++) {
            Object obj = tables.get(i);
            String table_name = (String) obj.getClass().getMethod("getTable_name").invoke(obj);
            String db_name = (String) obj.getClass().getMethod("getDb_name").invoke(obj);
            String table_comment = (String) obj.getClass().getMethod("getTable_comment").invoke(obj);
            String db_table = db_name + "." + table_name;
            if (db_table.equals(find_table_name)) {
                return table_comment;
            }
        }
        return "";
    }

    public static String getColumnCommon(ArrayList<Object> tables, String find_table_name, String find_column_name) throws Exception {
        for (int i = 0; i < tables.size(); i++) {
            Object obj = tables.get(i);
            String table_name = (String) obj.getClass().getMethod("getTable_name").invoke(obj);
            String db_name = (String) obj.getClass().getMethod("getDb_name").invoke(obj);
            String column_comment = (String) obj.getClass().getMethod("getColumn_comment").invoke(obj);
            String column_name = (String) obj.getClass().getMethod("getColumn_name").invoke(obj);
            String table_column = table_name + "." + column_name;
            String db_table = db_name + "." + table_name;
            if (db_table.equals(find_table_name) && find_column_name.equals(table_column)) {
                return column_comment;
            }
        }
        return "";
    }

    private static JSONObject updateColumn(JSONObject column, ArrayList<Object> tables, String table_name) throws Exception {
        String column_name = column.getString("column_name");
        String column_comment = column.getString("column_comment");
        String new_column_comment = getColumnCommon(tables, table_name, column_name);
        if (new_column_comment.length() > 0) {
            String[] comments = column_comment.split(" | ");
            if (comments.length >= 2) {
                new_column_comment = new_column_comment + " | " + comments[2];
                column.put("column_comment", new_column_comment);
            } else {
                column.put("column_comment", new_column_comment);
            }
        }
        return column;
    }

    public static String loadModel(SqlRequestBody sqlReq) throws Exception {
        Connection metaConn = null;
        MxResponseBody resp = new MxResponseBody();
        CommonResponseBody comm = new CommonResponseBody();
        comm.setSuccess();
        resp.setCommon(comm);
        try {
            metaConn = MetaMysql.connect();
            String sql =
                    "select  a.db_name, a.table_name, a.table_comment, b.column_name, b.column_comment\n" + " " +
                            "from " +
                            "mxgc_user_table a, mxgc_user_column b where a.db_name = b.db_name and a.table_name = b" + ".table_name" + "\n" + "order by a.db_name, a.table_name, b.ordinal_position";
            ResultSet rs = MetaMysql.querySql(metaConn, sql);
            ArrayList<Object> tables = getResultSet(rs);
            sql =
                    "select mxmc, mxnr, mlid, mxlx, mxsm, sfbq, bqmlid from mxgc_user_mxlb where id = '" + sqlReq.getMxid() + "'";
            rs = MetaMysql.querySql(metaConn, sql);
            if (rs.next()) {
                resp.setMxlx(rs.getString("mxlx"));
                String mxnr = rs.getString("mxnr").replace("\\\"", "\"");
                JSONObject json = JSONObject.parseObject(mxnr);
                JSONArray cells = json.getJSONArray("cells");
                for (int i = 0; i < cells.size(); i++) {
                    JSONObject cell = cells.getJSONObject(i);
                    String shape = cell.getString("shape");
                    if (shape.equals("edge")) {
                        continue;
                    }
                    JSONObject data = cell.getJSONObject("data");
                    JSONObject table = data.getJSONObject("table");
                    JSONObject component = data.getJSONObject("component");
                    //更新表注释
                    String table_name = table.getString("table_name");
                    String new_table_comment = getTableCommon(tables, table_name);
                    if (new_table_comment.length() > 0) {
                        table.put("table_comment", new_table_comment);
                    }
                    if (shape.equals("bsrNode")) {
                        if (new_table_comment.length() > 0) {
                            component.put("table_comment", new_table_comment);
                        }
                    }
                    //更新语言
                    if (data.getString("category").equals("bsc_")) {
                        if (table.get("langType") == null) {
                            table.put("langType", "sql_");
                        } else {
                            table.put("langType", table.get("langType"));
                        }
                    }
                    //更新列注释
                    JSONArray columns = data.getJSONArray("columns");
                    for (int j = 0; j < columns.size(); j++) {
                        JSONObject column = columns.getJSONObject(j);
                        column = updateColumn(column, tables, table_name);
                        columns.set(j, column);
                    }

                    JSONArray groupBys = data.getJSONArray("groupBys");
                    for (int j = 0; j < groupBys.size(); j++) {
                        JSONObject column = groupBys.getJSONObject(j);
                        column = updateColumn(column, tables, table_name);
                        groupBys.set(j, column);
                    }
                    JSONArray groupCalcs = data.getJSONArray("groupCalcs");
                    for (int j = 0; j < groupCalcs.size(); j++) {
                        JSONObject column = groupCalcs.getJSONObject(j);
                        column = updateColumn(column, tables, table_name);
                        groupCalcs.set(j, column);
                    }

                    data.put("component", component);
                    data.put("table", table);
                    data.put("columns", columns);
                    data.put("groupBys", groupBys);
                    data.put("groupCalcs", groupCalcs);
                    cell.put("data", data);
                    cells.set(i, cell);
                }
                mxnr = JSONObject.toJSONString(cells);
                resp.setMxnr(mxnr);
                resp.setMxmc(rs.getString("mxmc"));
                resp.setMlid(rs.getString("mlid"));
                resp.setMxsm(rs.getString("mxsm"));
                resp.setSfbq(rs.getString("sfbq"));
                resp.setBqmlid(rs.getString("bqmlid"));
            } else {
                resp.setMxlx("1");
                resp.setMxnr("");
                resp.setMxmc("我的模型");
                resp.setMlid("100");
                resp.setMxsm("我的说明");
                resp.setSfbq("0");
                resp.setBqmlid("300");
            }
            String respJson = JSONObject.toJSONString(resp);
            return respJson;
        } finally {
            closeDb(metaConn);
        }
    }


    public static String reqVerify(SqlRequestBody sqlReq) throws Exception {
        Connection metaConn = null;
        CommonResponseBody resp = new CommonResponseBody();
        resp.setSuccess();
        try {

            metaConn = MetaMysql.connect();
            //为防止非法访问，要求权限的获取间隔为
            String sql = "SELECT\n" +
                    "\tb.user_id,\n" +
                    "\ta.user_name AS login_name,\n" +
                    "\tb.user_name,\n" +
                    "\te.data_scope, b.dept_id \n" +
                    "FROM\n" +
                    "\tmxgc_user_login_log a,\n" +
                    "\tsys_user b,\n" +
                    "\t(\n" +
                    "\tSELECT\n" +
                    "\t\tb.login_name,\n" +
                    "\t\tmax( data_scope ) data_scope \n" +
                    "\tFROM\n" +
                    "\t\tsys_user b,\n" +
                    "\t\tsys_role c,\n" +
                    "\t\tsys_user_role d \n" +
                    "\tWHERE\n" +
//                    "\t\tb.login_name = '"+sqlReq.getUserName()+"' \n" +     //jqh 7.13
                    "\t\t b.user_id = d.user_id \n" +
                    "\t\tAND d.role_id = c.role_id group by b.login_name  \n" +  //jqh 7.13
                    "\t) e \n" +
                    "WHERE\n" +
                    "\ta.user_name = b.login_name \n" +
                    "\tAND e.login_name = b.login_name \n" +
                    "\tAND a.id = '" + sqlReq.getMxid() + "' \n" +
                    "\tAND TIMESTAMPDIFF(SECOND, a.login_time, CURRENT_TIMESTAMP ()) < 3000";
            ResultSet rs = MetaMysql.querySql(metaConn, sql);
            if (!rs.next()) {
                resp.setFail("没有登录权限，请重新登录");
            } else {
                String login_name = rs.getString("login_name");
                String user_name = rs.getString("user_name");
                String data_scope = rs.getString("data_scope");
                String dept_id = rs.getString("dept_id");
                String id = rs.getString("user_id");
                String roleStr = getRoleStr(login_name, metaConn);
                if (roleStr.length() <= 0) {
                    resp.setFail("没有登录权限，请重新登录");
                } else {
                    sqlReq.setUserName(login_name);
                    resp.setDatas(getMyDefaultDbName(sqlReq));
                    resp.setData1s(getPassTables(roleStr));
                    String extraStr =
                            login_name + "|" + roleStr + "|" + id + "|" + data_scope + "|" + dept_id;
                    resp.setExtraStr(extraStr);
                    //权限
                    Token token = new Token();
                    token.setLoginName(login_name);
                    token.setUserName(user_name);
                    JettyServer.setGlobalToken(id, token);
                }
            }
            String respJson = resultSuccess(resp);
            return respJson;
        } finally {
            closeDb(metaConn);
        }
    }

    public static ArrayList<Object> getPassTables(String roleStr) throws Exception {
        Connection metaConn = null;
        ArrayList<Object> tables = new ArrayList<>();
        try {
            metaConn = MetaMysql.connect();
            String sql = "select distinct table_id from sys_role_table where role_id in (" + roleStr + ")";
            ResultSet rs = MetaMysql.querySql(metaConn, sql);
            tables = getResultSet(rs);
        } finally {
            closeDb(metaConn);
        }
        return tables;
    }

    public static ArrayList<Object> getMyDefaultDbName(SqlRequestBody sqlReq) throws Exception {
        Connection metaConn = null;
        ArrayList<Object> dbNames = new ArrayList<>();
        try {
            Map<String, Object> addProperties = new HashMap();
            DynamicBean dynamicBean = new DynamicBean();
            addProperties.put("db_name", "self_owned");
            addProperties.put("db_comment", "");
            Object obj = ReflectUtil.getTarget(dynamicBean, addProperties);
            dbNames.add(obj);
        } finally {
            closeDb(metaConn);
        }
        return dbNames;
    }


    public static String getTableUpdateName(String content) {
        content = content.replaceAll("\\\\", "");
        JSONObject contentJson = JSONObject.parseObject(content);
        JSONArray cells = contentJson.getJSONArray("cells");
        for (int i = 0; i < cells.size(); i++) {
            JSONObject cell = cells.getJSONObject(i);
            String shape = cell.getString("shape");
            if (shape.equals("edge")) {
                continue;
            }
            JSONObject data = cell.getJSONObject("data");

            if (data.getString("category").equals("bsc_")) {
                if (data.getString("table_update_name") == null) {
                    return "";
                }
                return data.getString("table_update_name");
            }
        }
        return "";
    }

    public static void writeMeta(SqlRequestBody sqlReq, String tableId) throws Exception {
        Connection metaConn = null;
        String myDbName = getMyDbName(sqlReq.getTableName());
        try {
            metaConn = MetaMysql.connect();
            String sql = "";

            String tableName = dealdot(sqlReq.getTableName());
            if (tableName.length() > 1) {
                //删除输出表信息
                sql = "delete from mxgc_user_table where user_name = '" + sqlReq.getUserName() + "' and table_name = "
                        + "'" + tableName + "'";
                MetaMysql.execSql(metaConn, sql);
                //插入输出表信息
                sql = "insert into mxgc_user_table(id, user_name, table_name, table_comment, db_name, " + "jlgs, " +
                        "table_type, sql_content, ai_type, input_tables, mxid)" + " values('" + tableId + "','" + sqlReq.getUserName() + "','" + tableName + "','" + sqlReq.getTableComment() + "','" + myDbName + "','0', '1', '" + sqlReq.getSql().replace("'", "''") + "','" + sqlReq.getLangType() + "','" + sqlReq.getInputTables() + "','" + sqlReq.getMxid() + "')";
                MetaMysql.execSql(metaConn, sql);
                //插入standard_table
                sql = "delete from standard_table where id = '" + tableId + "'";
                MetaMysql.execSql(metaConn, sql);
                sql =
                        "insert into standard_table(id, table_name, table_comment, mlid, update_time, table_type, " +
                                "bz1," +
                                "user_name, is_share) values('" + tableId + "','" + tableName + "','" + sqlReq.getTableComment() +
                                "','" + sqlReq.getMlid() + "','" + getNowDate() + "','self_owned','" + sqlReq.getMxid() + "','" + sqlReq.getUserName() + "','')";
                MetaMysql.execSql(metaConn, sql);

                String tableUpdateName = getTableUpdateName(sqlReq.getMxnr());
                if (tableUpdateName.length() > 0) {
                    sql = "update standard_table set bz1 = 'modal' where concat_ws('.', table_type, table_name) = '"
                            + tableUpdateName + "'";
                    MetaMysql.execSql(metaConn, sql);
                }
            }
            //管理JSON信息
            if (sqlReq.getMxid().length() > 0) {
                sql =
                        "select * from mxgc_user_mxlb where id = '" + sqlReq.getMxid() + "' and user_name='" + sqlReq.getUserName() + "'";
                ResultSet rs = MetaMysql.querySql(metaConn, sql);

                if (!rs.next()) {
                    //插入JSON信息
                    sql =
                            "insert into mxgc_user_mxlb(id, mxmc, mxlx, user_name, mxnr, mlid, mxsm, mxtp, " + "sfbq,"
                                    + " " + "bqmlid" + ") values('" + sqlReq.getMxid() + "', '" + sqlReq.getMxmc() +
                                    "', " + "'1'," + "'" + sqlReq.getUserName() + "','" + sqlReq.getMxnr() + "','" + sqlReq.getMlid() + "','" + sqlReq.getMxsm() + "','" + sqlReq.getMxtp() + "','" + sqlReq.getSfbq() + "','" + sqlReq.getBqmlid() + "')";
                    MetaMysql.execSql(metaConn, sql);
                } else {
                    sql =
                            "update mxgc_user_mxlb set mxmc = '" + sqlReq.getMxmc() + "', mxnr = '" + sqlReq.getMxnr() + "',"
                                    + " mlid = '" + sqlReq.getMlid() + "', mxsm = '" + sqlReq.getMxsm() + "', mxtp = "
                                    + "'" + sqlReq.getMxtp() + "', sfbq = '" + sqlReq.getSfbq() + "', bqmlid = '" + sqlReq.getBqmlid() + "', create_time = '" + getNowDate() + "' where id = '" + sqlReq.getMxid() + "'";
                    MetaMysql.execSql(metaConn, sql);
                }
            }
        } finally {
            closeDb(metaConn);
        }
    }


    public static void writeColumn(SqlRequestBody sqlReq, String tableId) throws Exception {
        Connection metaConn = null;
        Connection dbConn = null;
        String myDbName = getMyDbName(sqlReq.getTableName());
        try {
            String tableName = dealdot(sqlReq.getTableName());
            if (tableName.length() <= 0) {
                return;
            }
            dbConn = DBSql.connect();
            String sql = "select * from " + myDbName + "." + tableName + " where 1=2";
            ResultSet rs = DBSql.querySql(dbConn, sql);
            ArrayList<SqlResponseResultMeta> metas = new ArrayList();
            ResultSetMetaData rsd = rs.getMetaData();
            for (int i = 1; i <= rsd.getColumnCount(); i++) {
                SqlResponseResultMeta meta = new SqlResponseResultMeta();
                meta.setField(rsd.getColumnLabel(i));
                String data_type = rsd.getColumnTypeName(i).toLowerCase();
                data_type = convertFieldType(data_type);
                meta.setType(data_type);
                meta.setSize(rsd.getColumnDisplaySize(i));
                metas.add(meta);
            }
            metaConn = MetaMysql.connect();

            if (tableName.length() > 1) {
                sql = "delete from standard_column where table_id = '" + tableId + "'";
                MetaMysql.execSql(metaConn, sql);
                sql = "insert into standard_column(id, table_id, column_name, column_comment, column_order, " +
                        "column_type, column_size) values('";
                for (int i = 0; i < sqlReq.getMetas().size(); i++) {
                    SqlResponseResultMeta meta = sqlReq.getMetas().get(i);
                    int k = -1;
                    for (int j = 0; j < metas.size(); j++) {
                        if (metas.get(j).getField().equals(meta.getField())) {
                            k = j;
                        }
                    }
                    String fieldType = "varchar";
                    if (k >= 0) {
                        fieldType = metas.get(k).getType();
                    }
                    String fieldSize = "0";
                    if (k >= 0) {
                        fieldSize = metas.get(k).getSize().toString();
                    }
                    String sql1 = getUUID() + "','" + tableId + "','" + meta.getField() + "','" + meta.getName() +
                            "','" + String.valueOf(i) + "','" + fieldType + "','" + fieldSize + "')";
                    MetaMysql.execSql(metaConn, sql + sql1);
                }
            }
        } finally {
            closeDb(metaConn);
            closeDb(dbConn);
        }
    }

    public static ArrayList<String> getColumn(SqlRequestBody sqlReq) {
        ArrayList<String> columns = new ArrayList();
        ArrayList<SqlResponseResultMeta> metas = sqlReq.getMetas();
        for (int i = 0; i < metas.size(); i++) {
            SqlResponseResultMeta meta = metas.get(i);
            columns.add(meta.getField());
        }
        return columns;
    }


    private static String getShowRow(SqlRequestBody sqlReq, Connection metaConn) throws Exception {
        String sql =
                "select save_row, show_row, create_table from mxgc_user_config where user_name = '" + sqlReq.getUserName() + "'";
        ResultSet rs_conf = MetaMysql.querySql(metaConn, sql);
        String show_row = "300";
        if (rs_conf.next()) {
            show_row = rs_conf.getString("show_row");
        }
        return show_row;
    }

    public static String reqShowData(SqlRequestBody sqlReq, JSONObject bodyJson) throws Exception {
        long beginTime = Calendar.getInstance().getTimeInMillis();
        Connection dbConn = null;
        Connection metaConn = null;
        Connection aiConn = null;
        try {
            dbConn = DBSql.connect();
            metaConn = MetaMysql.connect();
            //根据用户得到返回行数，限制配置
            String show_row = getShowRow(sqlReq, metaConn);
            String sql = "";
            ResultSet rs = null;
            String sqlCount = "";
            if (sqlReq.getLangType().startsWith("sql_")) {
                //非AI分支
                //计数
                sql = "select count(1) from( " + sqlReq.getSql() + ") sql_count";
                rs = DBSql.querySql(dbConn, sql);
                rs.next();
                sqlCount = rs.getString(1);
                //查询数据
                if (DBSql.dbType.equals("oracle")) {
                    sql = "select * from (" + sqlReq.getSql() + ") where rownum <= " + show_row;
                } else {
                    if (DBSql.dbType.equals("sqlServer")) {
                        sql = "select top " + show_row + " * from (" + sqlReq.getSql() + ") a";
                    } else {
                        sql = "select * from (" + sqlReq.getSql() + ") show_data limit " + show_row;
                    }
                }
                rs = DBSql.querySql(dbConn, sql);
            }
            if (sqlReq.getLangType().startsWith("ai_")) {
                //AI分支
                aiConn = AISql.connect();
                sql = sqlReq.getSql();
                String[] sqls = sql.split(";");
                //删除目标表
                if (sqls[0].length() > 0) {
                    AISql.execSql(aiConn, sqls[0]);
                }
                //执行AI算法
                if (sqls[1].length() > 0) {
                    AISql.querySql(aiConn, sqls[1]);
                }
                //返回执行结果
                rs = AISql.querySql(aiConn, sqls[2]);
            }
            if (sqlReq.getLangType().startsWith("api_")) {
                JSONObject parameters = bodyJson.getJSONObject("parameters");
                String resultTableName = parameters.getString("resultTableName");
                String left_sql = parameters.getString("left_sql");
                String right_sql = parameters.getString("right_sql");
                String train_data_column = parameters.getString("train_data_column");
                String train_target_column = parameters.getString("train_target_column");
                String train_id_column = parameters.getString("train_id_column");
                String predict_data_column = parameters.getString("predict_data_column");
                String predict_id_column = parameters.getString("predict_id_column");
                String algorithm_type = parameters.getString("algorithm_type");
                Object params = parameters.get("params");
                if (train_target_column.length() > 0) {
                    left_sql = "select " + train_id_column + "," + train_data_column + "," + train_target_column + " "
                            + "from" + " (" + left_sql + ") a limit 1000000";
                } else {
                    left_sql = "select " + train_id_column + "," + train_data_column + " from" + " (" + left_sql + ")" +
                            " a limit 1000000";
                }
                rs = DBSql.querySql(dbConn, left_sql);
                ArrayList<Object> train_data = getResultSet(rs);
                right_sql = "select " + predict_id_column + "," + predict_data_column + " from (" + right_sql + ") a "
                        + "limit 1000000";
                rs = DBSql.querySql(dbConn, right_sql);
                ArrayList<Object> predict_data = getResultSet(rs);
                ApiAlgorithmRequest apiReq = new ApiAlgorithmRequest();
                apiReq.setTrain_data(train_data);
                apiReq.setPredict_data(predict_data);
                apiReq.setTrain_id_column(train_id_column);
                apiReq.setTrain_target_column(train_target_column);
                apiReq.setPredict_id_column(predict_id_column);
                apiReq.setAlgorithm_type(algorithm_type);
                apiReq.setParams(params);
                String jsonStr = JSONObject.toJSONString(apiReq);
                Map<String, String> heads = new HashMap<>();
                String respBody = HttpClient.postHttp(algorithmUrl, jsonStr, heads);
                System.out.println(respBody);
                JSONObject jo = JSONObject.parseObject(respBody);
                String code = jo.getString("code");
                String message = jo.getString("message");
                if (!code.equals("0")) {
                    throw new Exception("算法执行失败:" + message);
                }
                sql = "drop table if exists " + resultTableName;
                DBSql.execSql(dbConn, sql);
                sql = "create table " + resultTableName + "(id varchar(40), predicted varchar(40))";
                DBSql.execSql(dbConn, sql);
                JSONArray data = jo.getJSONArray("data");
                for (int i = 0; i < data.size(); i++) {
                    JSONObject row = data.getJSONObject(i);
                    String id = row.getString("id");
                    String predicted = row.getString("predicted");
                    sql = "insert into " + resultTableName + " values('" + id + "','" + predicted + "')";
                    DBSql.execSql(dbConn, sql);
                }
                sqlCount = String.valueOf(data.size());
                sql = sqlReq.getSql() + " limit " + show_row;
                rs = DBSql.querySql(dbConn, sql);
            }
            SqlResponseBody resp = new SqlResponseBody();
            resp.setCommon(getCommonResponseBody(beginTime));
            String returnStr = getSqlResponseResult(sqlReq, rs, resp, metaConn, sqlCount, show_row);
            return returnStr;
        } finally {
            closeDb(dbConn);
            closeDb(metaConn);
            closeDb(aiConn);
        }
    }

    public static String reqShowDataBySimple(SqlRequestBody sqlReq) throws Exception {
        long beginTime = Calendar.getInstance().getTimeInMillis();
        Connection dbConn = null;
        Connection metaConn = null;
        try {
            dbConn = DBSql.connect();
            metaConn = MetaMysql.connect();
            sqlReq = setSqlRequestBodyMetas(sqlReq);
            sqlReq.setMxnr("");
            sqlReq.setSql("");

            ResultSet rs = null;
            String sql = "select * from " + sqlReq.getTableName() + " limit 100";
            rs = DBSql.querySql(dbConn, sql);
            SqlResponseBody resp = new SqlResponseBody();
            resp.setCommon(getCommonResponseBody(beginTime));
            String returnStr = getSqlResponseResult(sqlReq, rs, resp, metaConn, "0", "100");
            return returnStr;
        } finally {
            closeDb(dbConn);
            closeDb(metaConn);
        }
    }

    private static SqlRequestBody setSqlRequestBodyMetas(SqlRequestBody sqlReq) throws Exception {
        Connection metaConn = null;
        try {
            metaConn = MetaMysql.connect();
            String tableName = sqlReq.getTableName();
            if (!tableName.startsWith("'")) {
                tableName = "'" + tableName + "'";
            }
            //得到用户配置
            String sql = "select column_name, column_comment from mxgc_user_column " + "where concat_ws('.', db_name,"
                    + " table_name) in(" + tableName + ") order by ordinal_position";
            ResultSet rs_conf = MetaMysql.querySql(metaConn, sql);
            ArrayList<SqlResponseResultMeta> metas = new ArrayList();
            while (rs_conf.next()) {
                SqlResponseResultMeta meta = new SqlResponseResultMeta();
                meta.setField(rs_conf.getString(1));
                meta.setName(rs_conf.getString(2));
                metas.add(meta);
            }
            sqlReq.setMetas(metas);
            return sqlReq;
        } finally {
            closeDb(metaConn);
        }

    }

    public static String reqShowDataByLLM(SqlRequestBody sqlReq) throws Exception {
        long beginTime = Calendar.getInstance().getTimeInMillis();
        Connection dbConn = null;
        Connection metaConn = null;
        String respBody = "";
        try {
            dbConn = DBSql.connect();
            metaConn = MetaMysql.connect();
            String sql = "select column_name, column_comment from mxgc_user_column where concat_ws('.', db_name, " +
                    "table_name) = '" + sqlReq.getTableName() + "'";
            ResultSet rs = MetaMysql.querySql(metaConn, sql);
            String tableName = sqlReq.getTableName().split("\\.")[1];
            String prompt = "有多个数据库表，信息如下：\n表名为" + tableName + "，包含的属性为";
            ArrayList<SqlResponseResultMeta> metas = new ArrayList<>();
            while (rs.next()) {
                SqlResponseResultMeta meta = new SqlResponseResultMeta();
                meta.setName(rs.getString("column_comment"));
                meta.setField(rs.getString("column_name"));
                metas.add(meta);
                prompt = prompt + " " + tableName + "." + rs.getString("column_name") + ",";
            }
            sqlReq.setMetas(metas);
            prompt = prompt.substring(0, prompt.length() - 1);
            prompt = prompt + ";\n" + sqlReq.getMxnr() + "？";

            ChatQuestion chatQuestion = new ChatQuestion();
            String url = globalProperties.getProperty("chatGLM.api");
            chatQuestion.setPrompt(prompt);
            String jsonStr = JSONObject.toJSONString(chatQuestion);
            Map<String, String> heads = new HashMap<>();
            logger.info("jsonStr:" + jsonStr);
            respBody = HttpClient.postHttp(url, jsonStr, heads);
            logger.info("respBody:" + respBody);
            JSONObject respObj = (JSONObject) JSONObject.parse(respBody);
            sql = respObj.get("response").toString();
            int begin = sql.indexOf("```sql");
            if (begin >= 0) {
                sql = sql.substring(begin + 6);
                int end = sql.indexOf("```");
                if (end >= 0) {
                    sql = sql.substring(0, end);
                }
            }
            sql = sql.replaceAll(";", "");
            sql = sql.replaceAll(" " + tableName, " " + sqlReq.getTableName());

            String show_row = getShowRow(sqlReq, metaConn);
            sql = "select * from (" + sql + ") a limit " + show_row;
            rs = DBSql.querySql(dbConn, sql);
            SqlResponseBody resp = new SqlResponseBody();
            resp.setCommon(getCommonResponseBody(beginTime));
            sqlReq.setSql(respBody);
            String returnStr = getSqlResponseResult(sqlReq, rs, resp, metaConn, "0", "100");
            return returnStr;
        } finally {
            closeDb(dbConn);
            closeDb(metaConn);
        }
    }

    public static String getSqlResponseResult(SqlRequestBody sqlReq, ResultSet rs, SqlResponseBody resp,
                                              Connection metaConn, String sqlCount, String showCount) throws Exception {
        ArrayList<Object> datas = getResultSet(rs);
        SqlResponseResult result = new SqlResponseResult();
        result.setDatas(datas);
        if (sqlReq.getMetas().size() == 0) {
            //sql 组件 访问后台时， metas为空
            ArrayList<SqlResponseResultMeta> metas = new ArrayList();
            ResultSetMetaData rsd = rs.getMetaData();
            for (int i = 1; i <= rsd.getColumnCount(); i++) {
                SqlResponseResultMeta meta = new SqlResponseResultMeta();
                meta.setField(rsd.getColumnName(i));
                meta.setName(rsd.getColumnLabel(i));
                metas.add(meta);
            }
            sqlReq.setMetas(metas);
        } else {
            //删除多余的字段
            ArrayList<SqlResponseResultMeta> metas = new ArrayList();
            ResultSetMetaData rsd = rs.getMetaData();
            for (int i = 1; i <= rsd.getColumnCount(); i++) {
                for (int j = 0; j < sqlReq.getMetas().size(); j++) {
                    SqlResponseResultMeta meta = sqlReq.getMetas().get(j);
                    if (meta.getField().equals(rsd.getColumnName(i))) {
                        metas.add(meta);
                        break;
                    }
                }
            }
            sqlReq.setMetas(metas);
        }
        ArrayList<SqlResponseResultMeta> tmps = new ArrayList<>();
        for (int i = 0; i < sqlReq.getMetas().size(); i++) {
            SqlResponseResultMeta one = sqlReq.getMetas().get(i);
            String name = one.getName();
            name =
                    name.replaceAll(" \\| 字符类型", "").replaceAll(" \\| 数字类型", "").replaceAll(" \\| 日期类型", "").replaceAll(" " + "\\| 日期时间类型", "");
            one.setName(name);
            tmps.add(one);
        }
        result.setMetas(tmps);
        result.setColumns(getColumn(sqlReq));
        result.setSqlCount(sqlCount);
        result.setShowCount(showCount);
        result.setSql(sqlReq.getSql());
        resp.setResult(result);
        String respJson = JSONObject.toJSONString(resp);
        //记录查询日志
        String mxnr = sqlReq.getMxnr();
        mxnr = mxnr.replace("'", "''");
        String id = getUUID();
        String sql1 = sqlReq.getSql().replace("'", "''");
        //写日志
        String sql = "insert into mxgc_user_exec_log(id, user_name, sql_content, duration, return_record, mxid, mxnr)"
                + " " + "values('" + id + "', '" + sqlReq.getUserName() + "', '" + sql1 + "', '" + resp.getCommon().getQtime() + "','" + datas.size() + "','" + sqlReq.getMxid() + "', '" + mxnr + "')";
        MetaMysql.execSql(metaConn, sql);
        return respJson;
    }

    public static String judgeTableName(SqlRequestBody sqlReq) throws Exception {
        long beginTime = Calendar.getInstance().getTimeInMillis();
        Connection metaConn = null;
        try {
            metaConn = MetaMysql.connect();
            String tableName = dealdot(sqlReq.getTableName());
            String tableComment = sqlReq.getTableComment();
            String sql = "select user_name from mxgc_user_table where table_name = '" + tableName + "'" + " or  " +
                    "table_comment = '" + tableComment + "'";

            ResultSet rs = MetaMysql.querySql(metaConn, sql);
            while (rs.next()) {
                String user_name = rs.getString("user_name");
                if (!user_name.equals(sqlReq.getUserName())) {
                    return returnFail("输出表中文名称或英文名称重复");
                }
            }
            SqlResponseBody sqlResp = new SqlResponseBody();
            sqlResp.setCommon(getCommonResponseBody(beginTime));
            String respJson = JSONObject.toJSONString(sqlResp);
            return respJson;
        } finally {
            closeDb(metaConn);
        }
    }


    public static String reqCopyModel(SqlRequestBody sqlReq) throws Exception {
        long beginTime = Calendar.getInstance().getTimeInMillis();
        Connection metaConn = null;
        Connection dbConn = null;
        try {
            metaConn = MetaMysql.connect();
            String mx_id = sqlReq.getMxid();
            String sql = "select * from mxgc_user_mxlb where id = '" + mx_id + "'";
            ResultSet rs = MetaMysql.querySql(metaConn, sql);
            if (rs.next()) {
                String mxnr = rs.getString("mxnr");
                mxnr = updateBscTableName(mxnr, sqlReq);
                sqlReq.setMxnr(mxnr);
                mxnr = sqlReq.getMxnr();
                String mxmc = rs.getString("mxmc") + "_" + "复制";
                sql =
                        "insert into mxgc_user_mxlb(id, mxmc, mxlx, mxsm, user_name, mxnr, create_time, mlid, mxtp, " +
                                "sfbq, bqmlid)" + " values('" + getUUID() + "', '" + mxmc + "', '1', '" + rs.getString("mxsm") + "'," +
                                " '" + sqlReq.getUserName() + "', '" + mxnr + "', '" + getNowDate() + "', '100'," +
                                "'" + rs.getString("mxtp") + "', '0', '300')";
                MetaMysql.execSql(metaConn, sql);
            }
            SqlResponseBody resp = new SqlResponseBody();
            resp.setCommon(getCommonResponseBody(beginTime));
            String respJson = JSONObject.toJSONString(resp);
            return respJson;
        } finally {
            closeDb(metaConn);
            closeDb(dbConn);
        }
    }

    public static String updateBscTableName(String content, SqlRequestBody sqlReq) {
        JSONObject json = JSONObject.parseObject(content.replace("\\\"", "\""));
        JSONArray cells = json.getJSONArray("cells");
        for (int i = 0; i < cells.size(); i++) {
            JSONObject cell = cells.getJSONObject(i);
            String shape = cell.getString("shape");
            if (shape.equals("edge")) {
                continue;
            }
            JSONObject data = cell.getJSONObject("data");

            if (data.getString("category").equals("bsc_")) {
                JSONObject table = data.getJSONObject("table");
                String table_name = table.getString("table_name");
                String table_comment = table.getString("table_comment");
                table_comment = table_comment + "_" + "复制";
                table.put("table_comment", table_comment);
                String[] table_names = table_name.split(".bsc_");
                table_name = table_names[0] + ".bsc_" + sqlReq.getUserName() + "_" + MyDateUtils.getNowDateBySimple();
                table.put("table_name", table_name);
                table.put("alias", table_name);
                data.put("table", table);
                cell.put("data", data);
            }
            cells.set(i, cell);
        }
        json.put("cells", cells);
        return JSONObject.toJSONString(json);
    }

    public static String reqSaveModel(SqlRequestBody sqlReq) throws Exception {

        long beginTime = Calendar.getInstance().getTimeInMillis();
        Connection metaConn = null;
        Connection dbConn = null;
        try {
            metaConn = MetaMysql.connect();
            String sql =
                    "select * from(select count(*) cun, id from mxgc_user_mxlb where mxmc = '" + sqlReq.getMxmc() +
                            "' and " + "user_name = '" + sqlReq.getUserName() + "' group by id) a where cun >= 1";
            ResultSet rs = MetaMysql.querySql(metaConn, sql);
            if (rs.next()) {
                if (!sqlReq.getMxid().equals(rs.getString("id"))) {
                    return returnFail("模型名称重复");
                }
            }
            String tableId = getUUID();
            sql = "select id, user_name from standard_table where bz1 = '" + sqlReq.getMxid() + "'";
            rs = MetaMysql.querySql(metaConn, sql);
            if (rs.next()) {
                tableId = rs.getString("id");
                if (!sqlReq.getUserName().equals(rs.getString("user_name"))) {
                    return returnFail("输出表名称重复");
                }
            }
            SqlResponseBody resp = new SqlResponseBody();
            //非机器学习处理逻辑
            if (sqlReq.getLangType().startsWith("api_") || sqlReq.getLangType().startsWith("sql_")) {
                //得到用户配置
                sql =
                        "select save_row, show_row, create_table from mxgc_user_config where user_name = '" + sqlReq.getUserName() + "'";
                ResultSet rs_conf = MetaMysql.querySql(metaConn, sql);
                String save_row = "100000";
                if (rs_conf.next()) {
                    save_row = rs_conf.getString("save_row");
                }
                if (sqlReq.getBz().equals("true")) {
                    save_row = "0";
                }
                String tableName = sqlReq.getTableName();
                //如果有输出表，先删除，再创建
                if (tableName.length() > 1) {
                    dbConn = DBSql.connect();
                    sql = "select db_name, table_name from mxgc_user_table where mxid = '" + sqlReq.getMxid() + "'";
                    rs = MetaMysql.querySql(metaConn, sql);
                    while (rs.next()) {
                        if (DBSql.dbType.equals("oracle") || DBSql.dbType.equals("sqlServer")) {
                            sql = "drop table " + rs.getString("db_name") + "." + rs.getString("table_name");
                        } else {
                            sql = "drop table if exists " + rs.getString("db_name") + "." + rs.getString("table_name");
                        }
                        try {
                            DBSql.execSql(dbConn, sql);
                        } catch (Exception ex) {
                        }
                    }
                    sql = "delete from mxgc_user_table where mxid = '" + sqlReq.getMxid() + "'";
                    MetaMysql.execSql(metaConn, sql);
                    sql = "delete from mxgc_user_column where concat(db_name, table_name) in (select concat(db_name, "
                            + "table_name) from mxgc_user_table where mxid = '" + sqlReq.getMxid() + "')";
                    MetaMysql.execSql(metaConn, sql);
                    sql = "delete from standard_table where bz1 = '" + sqlReq.getMxid() + "'";
                    MetaMysql.execSql(metaConn, sql);


                    if (DBSql.dbType.equals("oracle") || DBSql.dbType.equals("sqlServer")) {
                        sql = "drop table " + tableName;
                    } else {
                        sql = "drop table if exists " + tableName;
                    }
                    try {
                        DBSql.execSql(dbConn, sql);
                    } catch (Exception ex) {
                    }
                    sql = sqlReq.getSql();
                    //创建表
                    if (DBSql.dbType.equals("gaussDb")) {
                        sql = "create table " + tableName + " with(orientation=column, compression=low) as " + sql +
                                " limit " + save_row;
                    } else {
                        if (DBSql.dbType.equals("oracle")) {
                            sql =
                                    "create table " + tableName + " as select * from (" + sql + ") where rownum <= " + save_row;
                        } else {
                            if (DBSql.dbType.equals("sqlServer")) {
                                sql = "select top " + save_row + " * into " + tableName + " from (" + sql + ") a ";
                            } else {
                                sql = "create table " + tableName + " as " + sql + " limit " + save_row;
                            }
                        }
                    }
                    DBSql.execSql(dbConn, sql);
                }
            }
            if (sqlReq.getLangType().startsWith("ai_")) {
                String[] sqls = sqlReq.getSql().split(";");
                dbConn = DBSql.connect();
                //其他算法，都在第2个sql中，生成了结果表
                if ((sqls[0].length() == 0 && sqls[1].length() == 0) || sqlReq.getLangType().equals("ai_apriori_")) {
                    sql = "drop table if exists " + sqlReq.getTableName();
                    try {
                        DBSql.execSql(dbConn, sql);
                    } catch (Exception ex) {
                    }
                    sql = "create table " + sqlReq.getTableName() + " as " + sqls[2];
                    DBSql.execSql(dbConn, sql);
                }
            }
            //注释
            addComment(sqlReq, dbConn);
            //写模型表
            writeMeta(sqlReq, tableId);
            //写表字段
            writeColumn(sqlReq, tableId);
            resp.setCommon(getCommonResponseBody(beginTime));
            String respJson = JSONObject.toJSONString(resp);
            return respJson;
        } finally {
            closeDb(metaConn);
            closeDb(dbConn);
        }
    }

    public static void addComment(SqlRequestBody sqlReq, Connection dbConn) throws Exception {
        if (DBSql.dbType.equals("gaussDb") || DBSql.dbType.equals("postgresql")) {
            ArrayList<String> sqls = new ArrayList<>();
            String tableName = sqlReq.getTableName();
            for (int i = 0; i < sqlReq.getMetas().size(); i++) {
                SqlResponseResultMeta meta = sqlReq.getMetas().get(i);
                String one = "COMMENT ON COLUMN " + tableName + "." + meta.getField() + " is '" + meta.getName() + "'";
                sqls.add(one);
            }
            String one = "COMMENT ON table " + tableName + " is '" + sqlReq.getTableComment() + "'";
            sqls.add(one);
            try {
                DBSql.batchExecSql(dbConn, sqls);
            } catch (Exception ex) {
            }
        }
    }

    public static String reqSaveComponent(SqlRequestBody sqlReq) throws Exception {

        long beginTime = Calendar.getInstance().getTimeInMillis();
        Connection metaConn = null;
        try {
            metaConn = MetaMysql.connect();
            String id = sqlReq.getMxid();
            String charConfig = sqlReq.getMxnr();
            String chartImage = sqlReq.getMxtp();
            String name = sqlReq.getMxmc();
            String issue = sqlReq.getMxsm();
            String mlid = sqlReq.getMlid();

            String sql =
                    "select * from(select count(*) cun, id from chart_component_list where name = '" + sqlReq.getMxmc() +
                            "'" + " and user_name = '" + sqlReq.getUserName() + "' group by id) a where cun >= 1";
            ResultSet rs = MetaMysql.querySql(metaConn, sql);
            if (rs.next()) {
                if (!sqlReq.getMxid().equals(rs.getString("id"))) {
                    return returnFail("名称重复");
                }

            }
            sql = "delete from chart_component_list where id = '" + id + "'";
            MetaMysql.execSql(metaConn, sql);
            sql = "insert into chart_component_list(id, chart_config, chart_image, name, issue, user_name, " +
                    "if_publish, mlid) " + "values('" + id + "', '" + charConfig + "', '" + chartImage + "', '" + name + "', "
                    + "'" + issue + "', '" + sqlReq.getUserName() + "', '1', '" + mlid + "')";
            MetaMysql.execSql(metaConn, sql);
            SqlResponseBody resp = new SqlResponseBody();
            resp.setCommon(getCommonResponseBody(beginTime));
            String respJson = JSONObject.toJSONString(resp);
            return respJson;
        } finally {
            closeDb(metaConn);
        }
    }

    public static String reqSaveScreen(SqlRequestBody sqlReq) throws Exception {

        long beginTime = Calendar.getInstance().getTimeInMillis();
        Connection metaConn = null;
        try {
            metaConn = MetaMysql.connect();
            String id = sqlReq.getMxid();
            String config = sqlReq.getMxnr();
            String image = sqlReq.getMxtp();
            String name = sqlReq.getMxmc();
            String issue = sqlReq.getMxsm();
            String mlid = sqlReq.getMlid();
            String sql =
                    "select * from(select count(*) cun, screen_id from chart_screen_list where name = '" + sqlReq.getMxmc() + "' and user_name = '" + sqlReq.getUserName() + "' group by screen_id) a where cun >= 1";
            ResultSet rs = MetaMysql.querySql(metaConn, sql);
            if (rs.next()) {
                if (!sqlReq.getMxid().equals(rs.getString("screen_id"))) {
                    return returnFail("名称重复");
                }

            }
            sql = "delete from chart_screen_list where screen_id = '" + id + "'";
            MetaMysql.execSql(metaConn, sql);
            sql = "insert into chart_screen_list(screen_id, screen_config, screen_image, name, issue, user_name, " +
                    "if_publish, mlid) values('" + id + "', '" + config + "', '" + image + "', '" + name + "', '" + issue +
                    "', '" + sqlReq.getUserName() + "', '1', '" + mlid + "')";
            MetaMysql.execSql(metaConn, sql);
            SqlResponseBody resp = new SqlResponseBody();
            resp.setCommon(getCommonResponseBody(beginTime));
            String respJson = JSONObject.toJSONString(resp);
            return respJson;
        } finally {
            closeDb(metaConn);
        }
    }


    public static String reqDeleteData(SqlRequestBody sqlReq) throws Exception {

        long beginTime = Calendar.getInstance().getTimeInMillis();
        Connection dbConn = null;
        Connection metaConn = null;
        try {
            metaConn = MetaMysql.connect();

            dbConn = DBSql.connect();
            String userName = sqlReq.getUserName();
            String tableName = sqlReq.getTableName();
            String dbName = tableName.substring(0, tableName.lastIndexOf("."));
            tableName = dealdot(tableName);
            //删除元数据
            String sql =
                    "delete from mxgc_user_table where user_name = '" + userName + "' and db_name = '" + dbName + "' " +
                            "and " + "table_name = '" + tableName + "'";
            MetaMysql.execSql(metaConn, sql);
            //删除实体表
            sql = "drop table " + dbName + "." + tableName;
            DBSql.execSql(dbConn, sql);
            CommonResponseBody common = getCommonResponseBody(beginTime);
            String respJson = resultSuccess(common);
            return respJson;
        } finally {
            closeDb(dbConn);
            closeDb(metaConn);
        }
    }

    public static String reqRemoveScreenImages(SqlRequestBody sqlReq) throws Exception {

        long beginTime = Calendar.getInstance().getTimeInMillis();
        Connection dbConn = null;
        Connection metaConn = null;
        try {
            metaConn = MetaMysql.connect();

            dbConn = DBSql.connect();
            String userName = sqlReq.getUserName();
            //删除元数据
            String sql =
                    "delete from chart_screen_images where user_name = '" + userName + "' and id = '" + sqlReq.getBz() + "'";
            MetaMysql.execSql(metaConn, sql);
            CommonResponseBody common = getCommonResponseBody(beginTime);
            String respJson = resultSuccess(common);
            return respJson;
        } finally {
            closeDb(dbConn);
            closeDb(metaConn);
        }
    }

    public static String reqSaveFile(SqlRequestBody sqlReq, JSONObject bodyJson) throws Exception {
        Connection dbConn = null;
        Connection metaConn = null;
        try {
            JSONArray columns = new JSONArray();
            for (int i = 0; i < sqlReq.getMetas().size(); i++) {
                SqlResponseResultMeta meta = sqlReq.getMetas().get(i);
                Integer size = meta.getSize() * 4;
                String fieldType = "";
                if (size > 4000) {
                    if (DBSql.dbType.equals("oracle")) {
                        fieldType = "clob";
                    } else {
                        fieldType = "text";
                    }
                } else {
                    if (DBSql.dbType.equals("oracle")) {
                        fieldType = "varchar2";
                    } else {
                        fieldType = "varchar";
                    }
                }
                JSONObject column = new JSONObject();
                column.put("column_name", meta.getField());
                column.put("column_comment", meta.getName());
                column.put("column_type", fieldType);
                column.put("column_size", size);
                column.put("column_order", i + 1);
                columns.add(column);
            }

            JSONObject tableJson = new JSONObject();
            String table_name = dealdot(sqlReq.getTableName());
            tableJson.put("table_name", table_name);
            tableJson.put("table_comment", sqlReq.getTableComment());
            String table_type = bodyJson.getString("table_type");
            tableJson.put("table_type", table_type);
            tableJson.put("extraColumns", columns);

            bodyJson.put("table", tableJson);
            String isNew = bodyJson.getString("isNew");
            if (isNew.equals("y")) {
                bodyJson.put("isCreate", "y");
                bodyJson.put("bz1", "execl");
                standard_create(bodyJson);
            } else {
                bodyJson.put("isCreate", "n");
            }

            dbConn = DBSql.connect();
            if (table_type.equals("original")) {
                table_name = "original." + table_name;
            }

            if (!sqlReq.getSql().equals("insert")) {
                //删除数据
                DBSql.execSql(dbConn, "delete from " + table_name);
            }


            //插入数据
            String fieldName = "";
            String fieldValue = "";
            for (int i = 0; i < sqlReq.getMetas().size(); i++) {
                SqlResponseResultMeta meta = sqlReq.getMetas().get(i);
                fieldName = fieldName + meta.getField() + ",";
                fieldValue = fieldValue + "?,";
            }
            fieldName = fieldName.substring(0, fieldName.length() - 1);
            fieldValue = fieldValue.substring(0, fieldValue.length() - 1);
            String sql = "insert into " + table_name + "(" + fieldName + ") values(" + fieldValue + ")";
            DBSql.batchExecSqlByParameter(dbConn, sql, sqlReq.getFileDatas(), sqlReq.getMetas());

            SqlResponseResult result = new SqlResponseResult();
            return setSuccessBodyByResult(result);

        } finally {
            closeDb(dbConn);
            closeDb(metaConn);
        }
    }


    public static String getResponse(Connection dbConn, String sql) throws Exception {
        SqlResponseBody resp = new SqlResponseBody();
        CommonResponseBody comm = new CommonResponseBody();
        comm.setSuccess();
        resp.setCommon(comm);

        //查询数据
        ResultSet rs = MetaMysql.querySql(dbConn, sql);
        ArrayList<Object> datas = getResultSet(rs);
        SqlResponseResult result = new SqlResponseResult();
        result.setDatas(datas);
        resp.setResult(result);
        String respJson = JSONObject.toJSONString(resp);
        return respJson;
    }


    private static ArrayList getExeclConfigTable(Connection metaConn) throws Exception {
        String sql = "select template_json from import_execl_config";
        ResultSet rs = MetaMysql.querySql(metaConn, sql);
        ArrayList<String> tables = new ArrayList<String>();
        while (rs.next()) {
            String template_json = rs.getString("template_json");
            JSONObject templateJson = JSONObject.parseObject(template_json);
            JSONArray jsonArray = templateJson.getJSONArray("tables");
            if (jsonArray == null) {
                jsonArray = templateJson.getJSONArray("sheets");
                ;
            }
            for (int i = 0; i < jsonArray.size(); i++) {
                JSONObject jsonObject = jsonArray.getJSONObject(i);
                String tableName = jsonObject.getString("table_name");
                tables.add(tableName);
            }
        }
        return tables;
    }

    private static ArrayList getApiConfigTable(Connection metaConn, SqlRequestBody sqlReq) throws Exception {

        ArrayList<String> tables = new ArrayList<String>();
        String dbStr = getFirstSelfDb(sqlReq.getUserName());
        String sql = "select distinct new_table_name from import_api_config_table";
        ResultSet rs1 = DBSql.querySql(metaConn, sql);
        while (rs1.next()) {
            String new_table_name = rs1.getString("new_table_name");
            if (new_table_name.indexOf(".") <= 0) {
                new_table_name = dbStr + "." + new_table_name;
            }
            tables.add(new_table_name);
        }
        return tables;
    }


    private static void findDir(JSONArray mlObjects, String mlid, JSONObject table) {
        for (int i = 0; i < mlObjects.size(); i++) {
            JSONObject mlObject = mlObjects.getJSONObject(i);
            if (mlObject.getString("key").equals(mlid)) {
                mlObject.getJSONArray("children").add(table);
                return;
            } else {
                JSONArray jsonChildrens = mlObject.getJSONArray("children");
                if (jsonChildrens == null) {
                    continue;
                }
                findDir(jsonChildrens, mlid, table);
            }
        }
    }

    public static String reqGetTables(SqlRequestBody sqlReq) throws Exception {

        Connection metaConn = null;
        try {
            metaConn = MetaMysql.connect();

            return getTreeBody(metaConn, sqlReq.getUserName());
        } finally {
            closeDb(metaConn);
        }
    }

    public static String reqGetPassTables(SqlRequestBody sqlReq) throws Exception {

        Connection metaConn = null;
        try {
            metaConn = MetaMysql.connect();
            String userName = sqlReq.getUserName();

            TreeBody body = new TreeBody();
            ArrayList<TreeResult> trees = new ArrayList<TreeResult>();
            //增加原始表逻辑
            trees.addAll(getTableByDir(userName, metaConn, "4000", true, ""));
            //增加共享模型
            trees.addAll(getTableByDir(userName, metaConn, "200", true, ""));

            body.setTables(trees);
            CommonResponseBody common = new CommonResponseBody();
            common.setSuccess();
            body.setCommon(common);
            String respJson = JSONObject.toJSONString(body);
            return respJson;
        } finally {
            closeDb(metaConn);
        }
    }

    public static ArrayList<TreeResult> getTableByDir(String userName, Connection metaConn, String parentId,
                                                      boolean isPass, String bz) throws Exception {
        ArrayList<TreeResult> treeResults = new ArrayList<>();
        SqlRequestBody sqlReq = new SqlRequestBody();
        sqlReq.setMlid("'" + parentId + "'");
        sqlReq.setUserName(userName);
        String respJson = getModelDir(sqlReq);
        //找到所有目录
        ReturnModelDir returnModelDir = JSONObject.parseObject(respJson, ReturnModelDir.class);
        treeResults.addAll(returnModelDir.getTreeResults());
        //找到目录下所有的表
        return getLabelTree(treeResults, metaConn, userName, parentId, isPass, bz);
    }

    //模型工厂得到表及其共享目录
    public static String getTreeBody(Connection metaConn, String userName) throws Exception {

        //权限控制
        String roleStr = getRoleStr(userName, metaConn);
        ArrayList<Object> tables = getPassTables(roleStr);
        JSONArray tableJsons = JSONObject.parseArray(JSONObject.toJSONString(tables));
        String tableStr = " id in (";
        for (int i = 0; i < tableJsons.size(); i++) {
            tableStr = tableStr + "'" + tableJsons.getJSONObject(i).get("table_id") + "',";
        }
        tableStr = tableStr.substring(0, tableStr.length() - 1) + ") ";
        if (tableStr.length() <= 10) {
            tableStr = " 1 = 1 ";
        }
        //权限控制
        TreeBody body = new TreeBody();
        ArrayList<TreeResult> trees = new ArrayList<TreeResult>();
        //增加考核目录逻辑
        trees.addAll(getTableByDir(userName, metaConn, "4100", false, tableStr));
        //增加原始表逻辑
        ArrayList<TreeResult> treeResults = new ArrayList<TreeResult>();
        treeResults.addAll(getTableByDir(userName, metaConn, "4000", false, tableStr));
        treeResults = removeTree(treeResults);
        trees.addAll(treeResults);
        //增加我的模型
        ArrayList<TreeResult> treeResults1 = new ArrayList<TreeResult>();
        treeResults1.addAll(getTableByDir(userName, metaConn, "100", false, tableStr));
        treeResults1 = removeTree(treeResults1);
        trees.addAll(treeResults1);
        //增加共享模型
        ArrayList<TreeResult> treeResults2 = new ArrayList<TreeResult>();
        treeResults2.addAll(getTableByDir(userName, metaConn, "200", false, tableStr));
        treeResults2 = removeTree(treeResults2);
        trees.addAll(treeResults2);

        body.setTables(trees);
        CommonResponseBody common = new CommonResponseBody();
        common.setSuccess();
        body.setCommon(common);
        String respJson = JSONObject.toJSONString(body);
        return respJson;
    }

    public static ArrayList<TreeResult> removeTree(ArrayList<TreeResult> treeResults) throws Exception {
        ArrayList<TreeResult> treeResults2 = new ArrayList<>();
        for (int i = 0; i < treeResults.size(); i++) {
            TreeResult treeResult = treeResults.get(i);
            if (! treeResult.getKey().startsWith("4100")){
                treeResults2.add(treeResult);
            }
        }
        return treeResults2;
    }

    public static ArrayList<TreeResult> getLabelTree(ArrayList<TreeResult> treeResults, Connection metaConn,
                                                     String userName, String parentId, boolean isPass,
                                                     String bz) throws Exception {
        for (int i = 0; i < treeResults.size(); i++) {
            TreeResult treeResult = treeResults.get(i);
            if (treeResult.getChildren() == null){
                treeResult.setChildren(new ArrayList<>());
            }

            if (treeResult.getChildren().size() > 0) {
                ArrayList<TreeResult> tmps2 = new ArrayList<>();
                ArrayList<Object> tmps1 = treeResult.getChildren();
                for (int j = 0; j < tmps1.size(); j++) {
                    String tmpstr1 = JSONObject.toJSONString(tmps1.get(j));
                    TreeResult treeResult1 = JSONObject.parseObject(tmpstr1, TreeResult.class);
                    tmps2.add(treeResult1);
                }
                tmps2 = getLabelTree(tmps2, metaConn, userName, parentId, isPass, bz);
                tmps1.clear();
                for (int j = 0; j < tmps2.size(); j++) {
                    String tmpstr1 = JSONObject.toJSONString(tmps2.get(j));
                    Object obj = JSONObject.parseObject(tmpstr1, Object.class);
                    tmps1.add(obj);
                }
                treeResult.setChildren(tmps1);
            }
            String sql = "";
            //我的模型
            if (parentId.equals("100")) {
                sql = "select concat_ws('.', 'self_owned', table_name) as value, case when length" + "(table_comment)" +
                        " <= 0 then table_name else table_comment end as title from standard_table" +
                        " where " +
                        "table_type = 'self_owned' and mlid = '" + treeResult.getKey() + "' and user_name = '" + userName
                        + "' order by update_time";
                if (isPass) {
                    sql = "select concat_ws('.', 'self_owned', table_name) as table_name, " +
                            "concat_ws('.', 't', id) as value, case when length" + "(table_comment) <= 0 then " +
                            "table_name else table_comment end as title\n" + " from " + "standard_table" + " where " +
                            "table_type = 'self_owned' and mlid = '" + treeResult.getKey() + "' and user_name = '" + userName
                            + "' order by update_time";
                }
            }
            //共享模型
            if (parentId.equals("200")) {
                sql = "select concat_ws('.', 'self_owned', table_name) as value, case when length" + "(table_comment)" +
                        " <= 0 then table_name else table_comment end as title\n" + " from " + "standard_table" + " " +
                        "where " +
                        "table_type = 'self_owned' and mlid = '" + treeResult.getKey() + "' and " + bz
                        + " order by update_time";
                if (isPass) {
                    sql = "select concat_ws('.', 'self_owned', table_name) as table_name, " +
                            "concat_ws('.', 't', id) as value, case when length" + "(table_comment) <= 0 then " +
                            "table_name else table_comment end as title\n" + " from " + "standard_table" + " where " +
                            "table_type = 'self_owned' and mlid = '" + treeResult.getKey() + "' order by update_time";
                }
            }
            //原始表
            if (parentId.equals("4000")) {
                sql = "select concat_ws('.', 'original', table_name) as value, case when length" + "(table_comment) " +
                        "<= 0 then table_name else table_comment end as title\n" + " from " + "standard_table" + " " +
                        "where " +
                        "table_type = 'original' and mlid = '" + treeResult.getKey()
                        + "' and ( (is_share = 'pass' and " + bz + ") or " +
                        " (user_name = '" + userName + "' and (is_share = '' or is_share is null) ) "
                        + ") order by update_time";
                if (isPass) {
                    sql = "select concat_ws('.', 'original', table_name) as table_name, " +
                            "concat_ws('.', 't', id) as value, case when length" + "(table_comment) <= 0 then " +
                            "table_name else table_comment end as title\n from standard_table where " +
                            "table_type = 'original' and mlid = '" + treeResult.getKey() + "' and is_share = 'pass' " +
                            "order " +
                            "by update_time";
                }
            }
            //考核目录
            if (parentId.equals("4100")) {
                sql = "select concat_ws('.', table_type, table_name) as value, case when length" + "(table_comment) " +
                        "<= 0 then table_name else table_comment end as title\n from standard_table " +
                        "where " +
                        "(table_type = 'original' and mlid = '" + treeResult.getKey()
                        + "' and ( (is_share = 'pass' and " + bz + ") or " +
                        " (user_name = '" + userName + "' and (is_share = '' or is_share is null) ) )) or " +
                        "(table_type = 'self_owned' and mlid = '" + treeResult.getKey() + "' and user_name = '"
                        + userName + "')"
                        + " order by update_time";
                if (isPass) {
                    sql = "select concat_ws('.', table_type, table_name) as table_name, " +
                            "concat_ws('.', 't', id) as value, case when length" + "(table_comment) <= 0 then " +
                            "table_name else table_comment end as title\n from standard_table where " +
                            "(table_type = 'original' and mlid = '" + treeResult.getKey() + "' and is_share = 'pass')" +
                            " or (table_type = 'self_owned' and mlid = '" + treeResult.getKey() + "' and user_name = '"
                            + userName + "')" +
                            "order by update_time";
                }
            }

            ResultSet rs1 = MetaMysql.querySql(metaConn, sql);
            ArrayList<Object> children = getResultSet(rs1);
            treeResult.getChildren().addAll(children);
            treeResults.set(i, treeResult);
        }
        return treeResults;
    }

    public static String reqGetColumns(SqlRequestBody sqlReq) throws Exception {
        Connection metaConn = null;
        try {
            metaConn = MetaMysql.connect();
            String roleStr = getRoleStr(sqlReq.getUserName(), metaConn);
            String tableName = sqlReq.getTableName();
            if (tableName == null) {
                tableName = "";
            }
            if (tableName.indexOf(".") >= 0) {
                tableName = dealdot(tableName);
            }
            //查询数据表
            String sql = "SELECT DISTINCT\n" + "\tconcat_ws( '.', table_type, table_name ) AS \n" + "VALUE\n" + "\t," +
                    "\n" + "CASE\n" + "\t\t\n" + "\t\tWHEN length( table_comment ) <= 0 THEN\n" + "\t\ttable_name " +
                    "ELSE " +
                    "table_comment \n" + "\tEND AS title, id \n" + "FROM\n" + "\tstandard_table \n" + "WHERE\n" + "\t" +
                    " " +
                    "table_name = '" + tableName + "'";
            ResultSet rs = MetaMysql.querySql(metaConn, sql);
            //查询数据列
            sql = "SELECT DISTINCT\n" + "\tconcat_ws( '.', '#', a.column_name ) AS \n" + "VALUE\n" + "\t,\n" + "CASE" +
                    "\t\n" + "\t\tWHEN length( a.column_comment ) <= 0 \n" + "\t\tOR a.column_comment IS NULL THEN\n" + "\t" +
                    "\t\tconcat_ws(' | ', a.column_name, a.column_type ) ELSE concat_ws(' | ', a.column_comment, a" +
                    ".column_type ) \n" + "\t\t\tEND AS title,\n" + "\t\ta.column_order as ordinal_position,\n" + "\t" +
                    "\ta" +
                    ".column_type AS type \n" + "\tFROM\n" + "\t\tstandard_column a \n" + "\tWHERE\n" + "\t\ttable_id" +
                    " = " +
                    "'$' \n" + "ORDER BY\n" + "\tcolumn_order + 0";
            TreeBody body = new TreeBody();
            ArrayList<TreeResult> trees = new ArrayList<TreeResult>();
            while (rs.next()) {
                TreeResult result = new TreeResult();
                result.setTitle(rs.getString("title"));
                result.setValue(rs.getString("value"));
                result.setKey(rs.getString("value"));
                String id = rs.getString("id");
                //找到数据库对应的表
                String tmpstr = dealdot(result.getValue());
                String sql1 = sql.replace("#", tmpstr);
                sql1 = sql1.replace("$", id);
                ResultSet rs1 = MetaMysql.querySql(metaConn, sql1);
                ArrayList<Object> children = getResultSet(rs1);
                result.setChildren(children);
                trees.add(result);
            }
            body.setTables(trees);
            CommonResponseBody common = new CommonResponseBody();
            common.setSuccess();
            body.setCommon(common);
            String respJson = JSONObject.toJSONString(body);
            return respJson;

        } finally {
            closeDb(metaConn);
        }
    }

    public static String dealdot(String tmpstr) {
        if (tmpstr.indexOf(".") >= 0) {
            tmpstr = tmpstr.substring(tmpstr.lastIndexOf(".") + 1, tmpstr.length());
        }

        return tmpstr;
    }

    //得到所有共享的分析组件及看板
    //input username
    public static String reqGetShareComponentAndScreen(SqlRequestBody sqlReq) throws Exception {
        Connection metaConn = null;
        try {
            metaConn = MetaMysql.connect();
            String roleStr = getRoleStr(sqlReq.getUserName(), metaConn);
            String userName = sqlReq.getUserName();
            TreeBody body = new TreeBody();
            ArrayList<TreeResult> trees = new ArrayList<TreeResult>();
            ArrayList<TreeResult> treeResults = new ArrayList<>();
            SqlRequestBody sqlReq1 = new SqlRequestBody();
            //分析组件
            sqlReq1.setMlid("'600'");
            sqlReq1.setUserName(userName);
            String respJson = getModelDir(sqlReq1);
            //找到所有目录
            ReturnModelDir returnModelDir = JSONObject.parseObject(respJson, ReturnModelDir.class);
            treeResults.addAll(returnModelDir.getTreeResults());
            //找到目录下所有的组件
            trees.addAll(getComponentByDir(treeResults, metaConn, userName, roleStr));
            //分析看板
            sqlReq1.setMlid("'800'");
            sqlReq1.setUserName(userName);
            respJson = getModelDir(sqlReq1);
            //找到所有目录
            returnModelDir = JSONObject.parseObject(respJson, ReturnModelDir.class);
            treeResults.clear();
            treeResults.addAll(returnModelDir.getTreeResults());
            //找到目录下所有的看板
            trees.addAll(getScreenByDir(treeResults, metaConn, userName, roleStr));
            body.setTables(trees);
            CommonResponseBody common = new CommonResponseBody();
            common.setSuccess();
            body.setCommon(common);
            String result = JSONObject.toJSONString(body);
            return result;
        } finally {
            closeDb(metaConn);
        }
    }

    public static ArrayList<TreeResult> getScreenByDir(ArrayList<TreeResult> treeResults, Connection metaConn,
                                                       String userName, String roleStr) throws Exception {
        for (int i = 0; i < treeResults.size(); i++) {
            TreeResult treeResult = treeResults.get(i);

            if (treeResult.getChildren().size() > 0) {
                ArrayList<TreeResult> tmps2 = new ArrayList<>();
                ArrayList<Object> tmps1 = treeResult.getChildren();
                for (int j = 0; j < tmps1.size(); j++) {
                    String tmpstr1 = JSONObject.toJSONString(tmps1.get(j));
                    TreeResult treeResult1 = JSONObject.parseObject(tmpstr1, TreeResult.class);
                    tmps2.add(treeResult1);
                }
                tmps2 = getScreenByDir(tmps2, metaConn, userName, roleStr);
                tmps1.clear();
                for (int j = 0; j < tmps2.size(); j++) {
                    String tmpstr1 = JSONObject.toJSONString(tmps2.get(j));
                    Object obj = JSONObject.parseObject(tmpstr1, Object.class);
                    tmps1.add(obj);
                }
                treeResult.setChildren(tmps1);
            }

            String sql = "select name as title, screen_id as value, screen_config as chart_config" + " from " +
                    "chart_screen_list where mlid = '" + treeResult.getKey() + "' order by create_time desc";
            ResultSet rs1 = MetaMysql.querySql(metaConn, sql);
            ArrayList<Object> children = getResultSet(rs1);
            treeResult.getChildren().addAll(children);
            treeResults.set(i, treeResult);
        }
        return treeResults;
    }

    //分析看板得到目录及其组件
    //input username
    public static String reqGetScreenTree(SqlRequestBody sqlReq) throws Exception {
        Connection metaConn = null;
        try {
            metaConn = MetaMysql.connect();
            String roleStr = getRoleStr(sqlReq.getUserName(), metaConn);
            String userName = sqlReq.getUserName();
            TreeBody body = new TreeBody();
            ArrayList<TreeResult> trees = new ArrayList<TreeResult>();
            ArrayList<TreeResult> treeResults = new ArrayList<>();
            SqlRequestBody sqlReq1 = new SqlRequestBody();
            sqlReq1.setMlid("'500', '600'");
            sqlReq1.setUserName(userName);
            String respJson = getModelDir(sqlReq1);
            //找到所有目录
            ReturnModelDir returnModelDir = JSONObject.parseObject(respJson, ReturnModelDir.class);
            treeResults.addAll(returnModelDir.getTreeResults());
            //找到目录下所有的组件
            trees.addAll(getComponentByDir(treeResults, metaConn, userName, roleStr));
            body.setTables(trees);
            CommonResponseBody common = new CommonResponseBody();
            common.setSuccess();
            body.setCommon(common);
            String result = JSONObject.toJSONString(body);
            return result;
        } finally {
            closeDb(metaConn);
        }
    }

    public static ArrayList<TreeResult> getComponentByDir(ArrayList<TreeResult> treeResults, Connection metaConn,
                                                          String userName, String roleStr) throws Exception {
        for (int i = 0; i < treeResults.size(); i++) {
            TreeResult treeResult = treeResults.get(i);
            if (treeResult.getChildren() == null){
                treeResult.setChildren(new ArrayList<>());
            }

            if (treeResult.getChildren().size() > 0) {
                ArrayList<TreeResult> tmps2 = new ArrayList<>();
                ArrayList<Object> tmps1 = treeResult.getChildren();
                for (int j = 0; j < tmps1.size(); j++) {
                    String tmpstr1 = JSONObject.toJSONString(tmps1.get(j));
                    TreeResult treeResult1 = JSONObject.parseObject(tmpstr1, TreeResult.class);
                    tmps2.add(treeResult1);
                }
                tmps2 = getComponentByDir(tmps2, metaConn, userName, roleStr);
                tmps1.clear();
                for (int j = 0; j < tmps2.size(); j++) {
                    String tmpstr1 = JSONObject.toJSONString(tmps2.get(j));
                    Object obj = JSONObject.parseObject(tmpstr1, Object.class);
                    tmps1.add(obj);
                }
                treeResult.setChildren(tmps1);
            }

            String sql =
                    "select name as title, id as value, chart_config" + " from chart_component_list where mlid " +
                            "=" + " " + "'" + treeResult.getKey() + "' ";
            if (treeResult.getKey().startsWith("500")) {
                sql = sql + " and user_name = '" + userName + "'";
            }
            sql = sql + " order by create_time desc";
            ResultSet rs1 = MetaMysql.querySql(metaConn, sql);
            ArrayList<Object> children = getResultSet(rs1);
            treeResult.getChildren().addAll(children);
            treeResults.set(i, treeResult);
        }
        return treeResults;
    }


}
